l****u
发帖数: 529 | 1 select table1.*, case when table1.col1=table2.col1 then 1 else 0 end as col4
from table1 left join table2 on table1.col1=table2.col1; |
|
t****a
发帖数: 1212 | 2 抛砖引玉：
DP, 复杂度O(n^3), n为第一个数组长度
1、设立表格Error: 2n x n，表格中每个单元(x, y)代表table1[1..y]与table2[1..x]
的比对最优解（误差平方和最小）.
2、最优解Error[2n, n] = Min (Error[2n-i, n-1] + (table1[n]-table2[2n]
)^2), in which 0 < i < n and i is int.
3. 记录最优解的路径
此解法可改进为O(n^2) |
|
F*******2
发帖数: 371 | 3 你code得到的是在table1 但是不在table2里面的数据
用下面code可以得到不在A B inner join中的数据：
SELECT A.ID1 AS A_ID1, A.ID2 AS A_ID2, A.ID3 as A_ID3, A.Value AS A_Value,
B.Value AS B_Value, B.ID1 AS B_ID1,B.ID2 AS B_ID2
FROM Table1 AS A
FULL OUTER JOIN Table2 AS B
ON A.ID1 = B.ID1 and A.ID2=B.ID2
WHERE A.ID1 IS NULL
OR B.ID1 IS NULL
Check here:
http://www.codeproject.com/Articles/33052/Visual-Representation |
|
l******9
发帖数: 579 | 4 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: sort two same tables SQL but different results
发信站: BBS 未名空间站 (Fri May 9 09:57:41 2014, 美东)
I am sorting two tables on SQL.
The two tables have the same column names and types and rows numbers.
I used order by to do sorting but the two tables are different in order.
Example,
SELECT *
FROM table1 AS t1
ORDER BY t1.col1 , t1.col2, t1.col3, t1.col4 ASC
SELECT *
FROM table2 AS t2
ORDER BY t2.col1 , t2.col2, t2.col3, t2.col4 ASC
T... 阅读全帖 |
|
l******9
发帖数: 579 | 5 I need to do a sql query in MS Access 2012.
But I got error in MS Access:
SELECT *
FROM
(
SELECT *
FROM table1
where not exists
(
SELECT *
FROM table2
where table2.id = table1.id
) as t
) as t1, table3
where table3.id = t1.id
Syntax error: (missing operator) in query expression 'not exists ( ... ) as
t'
Any help would be appreciated. |
|
c*****d
发帖数: 6045 | 6 -- not exists的subquery不要alias
SELECT *
FROM
(
SELECT *
FROM table1
where not exists
(
SELECT *
FROM table2
where table2.id = table1.id
)
) as t1, table3
where table3.id = t1.id |
|
s**********i
发帖数: 711 | 7 table1 has bunch of columns, with 'id' as primary key, and
I need to update a column a with max value of table2.b, for
corresponding id.
I tried
replace into table1 (id, a) select id, max(b) from table2 group by id;
this can put the max(b) into table1 a column, but in same time overwrites
all other columns in table1 to default value... which certainly is not
what I want.
anyone know an answer? TIA |
|
d****o
发帖数: 3 | 8 有两个table，
table1有ID, Descr两个字段， 其中ID为unique key
table2有ID1, ID2和其他一些字段，其中ID1，ID2都是从table1的ID来的，
想要talbe1.ID有delete, update的时候table2中的ID1, ID2会相应更新，
缺做不了，说会有may cause cycles or multiple cascade paths.
请问，应该怎样解决这个问题呀？
多谢 |
|
a*******t
发帖数: 891 | 9 what database are you using?
the query itself can't run in MS Access.
have you tried it in the database itself?
Table2 have only one record. table1 have more than 10,000 records. Want to get
max(date) and update table2.
run it and got error message:
"Operation Must Be Updatable Query". I know it is the link problem. No idea ho
w to deal with it or is there any other way to work on it? Thanks. Help please
. |
|
x****e
发帖数: 55 | 10 我有两个表，都只有一个属性 “ID”，每张表差不多都有5万条记录
比如
ID
10
13
300
100
12
1001
。。。
另外一个表也一样，只是记录的数据可能不同
现在要求2个表的交集，可以这么做
select * from table1, table2 where table1.ID = table2.ID;
但是因为表比较大，上面这个命令费时间太多(因为对所有记录两两比较， O(n^2))
按理说如果两个表先进行排序，后在求交集的话，时间复杂度只是线性的(O(2n))
但是我不知道怎么用SQL语言实现，请各位大牛帮忙
多谢 |
|
l******n
发帖数: 9344 | 11 I think what you mean is right, but the answer you give includes subquer
y(from (select ***))
My answer is:
select table1.Name, table2.** from table1, table 2 where table1.UserId =
table2.UserId |
|
a*******s
发帖数: 324 | 12 table1 10k
id not index or index
table2 100m
id not indexed
where table1.id = table2.id
They are almost the same. Database is postgre. Not sure if postgre will
index the table1.id in the memory. |
|
B*****g
发帖数: 34098 | 13 1. check if table2, table3 has primary key userid.
if not, add.
2.
insert into tables1(userid, nickname, msn)
select a.userid, a.nickname, b.msn
from table2 a, table3 b
where a.userid = b.userid.
1. check if table1 has primary key userid.
if not, add.
Note: IF userid is not unique in table 2, table3, kick the person who design
these tables.
records
first |
|
B*****g
发帖数: 34098 | 14 CREATE TABLE NewTable AS
SELECT *
FROM ( SELECT COL1,COL2 FROM Table1
UNION ALL
SELECT COL3,COL4 FROM Table2)
or
INSERT INTO NewTable
SELECT *
INTO NewTable
FROM ( SELECT COL1,COL2 FROM Table1
UNION ALL
SELECT COL3,COL4 FROM Table2) |
|
M***7
发帖数: 2420 | 15 guys, thanks a lot .
Finally I used the following query
=====================
INSERT INTO NewTalbe
SELECT col1, col2 FROM TABLE1
UNION ALL
SELECT col3 col4 FROM TABLE2
======================
It woks.
If I use
=================
CREATE TABLE newtable AS
SELECT *
FROM (SELECT col1, col2 FROM TABLE1
UNION ALL
SELECT col3 col4 FROM TABLE2)
===================
It still did not work.
Could anyone explain it a little bit detail for me? I am really a rookie in
SQL.
Thanks. |
|
p*c
发帖数: 421 | 16 【 以下文字转载自 JobHunting 讨论区 】
发信人: pic (喜欢画画么?), 信区: JobHunting
标 题: 问一道SQL的题
发信站: BBS 未名空间站 (Mon Feb 23 14:52:14 2009)
怎么在一个TABLE的一个格子里插入MULTIPLE VALUE?
举个例子有个TABLE1
employee_id, employee_name, employee_code
1 Mary a, b, c
2 Nancy a
3 John c, m
4 Leo b, a
employee_code是从另外一个table2读进来的
比如Mary在table2里
employee_id, employee_code
1 a
1 b
1 c
怎么写sql的语句能update tabl1, 把a,b,c全都写到field |
|
i*i
发帖数: 25 | 17 select employee_id,max(sys_connect_by_path(employee_code,',')) result from
(
select employee_id,employee_code,(row_number() over(order by employee
_id,employee_code desc) + rank() over(order by employee_id)) rn
from table2
)
connect by rn-1 = prior rn
group by employee_id;
oracle 9i或以上可实现，结果是对table2聚合
出现
employee_id employ_code
1 a,b,c
接下来就是再写个outer join了就不说了 |
|
t****n
发帖数: 263 | 18 This is easy in SQL Server
update t1
set t1.employee_code = comb
from TABLE1 t1 join
(
SELECT employee_id,
STUFF((SELECT ',' + employee_code AS [text()]
FROM TABLE2 AS G2
WHERE G2.employee_id = G1.employee_id
ORDER BY employee_code
FOR XML PATH('')), 1, 1, '') AS comb
FROM TABLE2 AS G1
GROUP BY employee_id
) t2
on t1.employee_id = t2.employee_id |
|
w*********g
发帖数: 2330 | 19 还是错误的，我很崩溃。
table1 的A列的数据，存在于 和table2 的A列，
但是 table1 中的B列有一些数据 不 存在于 table 2的B列，
想把table1和table2 中 A列相同，但是B列不存在的那些行选出来。
请看我的原文描述。 |
|
m**********2
发帖数: 2252 | 20 select t1.* from table1 t1
left join table2 t2
on t1.a = t2.a
where table2.a is null |
|
m**********2
发帖数: 2252 | 21 没有看到这个。
这样试试：
select * from table1 t1
where t1.a in
(select t2.a from table2 t2)
and t1.b not in
(select t2.b from table2 t2) |
|
M*****r
发帖数: 1536 | 22 选出table1.A=table2.A and table1.B=table2.B 的rows, 然后从table1里面减去这些
rows。
出， |
|
f*******h
发帖数: 53 | 23 select
m.loationID,table1.name,m.description
from
(select
a.locationID, table2.*
from
(select distinct locationID from table1) a cross join table2) m left outer
join table1 on m.name=table1.name |
|
B*****g
发帖数: 34098 | 24 NOT EXSITS (SELECT 1 FROM table2 t2 WHERE t2.no = t.no)
or
no NOT IN (SELECT no FROM table2 t2) |
|
r****r
发帖数: 1839 | 25 Table1:
MemberID
PurchaseID
Table2：
PurchaseID
ItemID
Date
LineNumber
给出Table2中所有行，满足如下条件：
同一个Member在同一天里购买多于一次ItemID=‘手表’
前年我会这个，今年忘记了。多谢。
|
|
n********6
发帖数: 1511 | 26 current output:
product, counter
apple 2
apple 3
orange x
Objective:
product, counter
apple 5
orange x
table1
transactionid, product
1, apple
2, orange
3, apple
...
m, banana
table2
transactionid, product
1, peach
2, apple
3, apple
...
n, apple
current code (to be modified)
select product, count(transactionid)
from table1
group by product
union
select product, count(transactionid)
from table2
group by product |
|
B*****g
发帖数: 34098 | 27 叫你朋友参加CINAOUG 9月7号的活动
table1(id, col1)
table2(id, col2)
SELECT id, col1, null AS col2
from table1
union all
SELECT ID, null, col2
from table2 |
|
g***l
发帖数: 18555 | 28 我还是没搞懂你想干什么，什么叫rank不同table里面的数据，你举个列子好不好，我
觉得你这个SP本身问题就很大，你有多少TABLE要RANK，RANK完了放在哪里？总不能就
扔在MANAGEMENT STUDIO里再COPY出来吧。为什么不能COMBINE呢
无非是这样么，RANK然后GROUP BY TABLENAME不就行了，这样就避免了有多少TABLE,
RANK出来放在哪里的问题。
TABLENAME DATA RANK
TABLE1 XXXX RANK
TABLE1 XXXX2 RANK
TABLE2 XXXX RANK
TABLE2 XXXX2 RANK |
|
k**g
发帖数: 1558 | 29 我要rank table1里的100 variables,结果存在table1里面，
接下来要rank table2里的同样的variables,结果存在table2里面，依次类推。。。 |
|
r*******n
发帖数: 3020 | 30 求指点。
我知道subquery 两种一种用 from table1， table2 where table1.id = table2.id；
第二种用 join；
第二种 performance比第一种要好。
还有用过with name as (select * from ...) 不知道算不算第三种 |
|
s**********o
发帖数: 14359 | 31 这两个有差异吗，看到有PROGRAMMER写了第一段，想改成第二段，就说不出为什么
Q1:
select
t1.*, t2.detail
from
table1 t1
left join
table2 t2 on t2.orderNo = t1.orderNo
Q2:
select
t1.*, t2.detail
from
table1 t1
left join
table2 t2 on t1.orderNo = t2.orderNo |
|
N**N
发帖数: 1713 | 32 SELECT distinct t1.[cid]
,t1.[eid]
FROM [Table1] as t1
inner join [Table2] as t2
on t1.cid=t2.cid
left join [Table2] as t3
on t1.eid=t3.eid
要这个效果？ |
|
N**N
发帖数: 1713 | 33 那用(select distinct eid from table2)之类的subquery代替table2就行 |
|
l******9
发帖数: 579 | 34 I am sorting two tables on SQL.
The two tables have the same column names and types and rows numbers.
I used order by to do sorting but the two tables are different in order.
Example,
col1 INT
col2 INT
col3 INT
col4 DOUBLE PRECISION
SELECT *
FROM table1 AS t1
ORDER BY t1.col1 , t1.col2, t1.col3, t1.col4 ASC
SELECT *
FROM table2 AS t2
ORDER BY t2.col1 , t2.col2, t2.col3, t2.col4 ASC
Table1 is :
col1 col2 col3 col4
80 790 3498 18654.064361
81 589 3182 2138518.05404
80 ... 阅读全帖 |
|
c*****d
发帖数: 6045 | 35 和其他字段无关，多半是col1字段类型不同
试着只对col1排序，并且转换成数值
oracle:
SELECT *
FROM table2 t2
ORDER BY to_number(t2.col1) asc
sql server:
SELECT *
FROM table2 t2
ORDER BY cast(t2.col1 as int) asc |
|
l******9
发帖数: 579 | 36 【 以下文字转载自 JobHunting 讨论区 】
发信人: light009 (light009), 信区: JobHunting
标 题: error of sql query in MS Access database
发信站: BBS 未名空间站 (Wed Oct 15 17:38:15 2014, 美东)
I need to do a sql query in MS Access 2012.
But I got error in MS Access:
SELECT *
FROM
(
SELECT *
FROM table1
where not exists
(
SELECT *
FROM table2
where table2.id = table1.id
) as t
) as t1, table3
where table3.id = t1.id
Syntax error: (missing operator) in query expression 'not exists ( ... ) as
t'
A... 阅读全帖 |
|
c*****d
发帖数: 6045 | 37 SELECT *
FROM
(
SELECT *
FROM table1
where not exists
(
SELECT *
FROM table2
where table2.id = table1.id
)
) as t1, table3
where table3.id = t1.id |
|
x******m
发帖数: 736 | 38 table1
id col1 col2 col3...
table2
col_id col_name
1 col1
2 col2
3 col3
现在想用sql生成table3，
id col_name col_value
其中id，col_vaule来自于table1，col_name来自于table2.
多谢。 |
|
S****e
发帖数: 10596 | 39 版上大大给看看，多谢啦！
第一个问题：
做一个产品查询，有3个表，table1用来放选择信息,table2&3 放显示信息
每个表都有一列放置unique ID
用select ID from table1 where blahblah
从table1里选择出一堆符合条件的ID
然后从table2 和 table3 中把 刚才选出ID 的信息 装入 dataset
有什么简单语句可以实现?
第二个问题：
装入dataset后用listview显示
要求按照随机顺序显示
有什么方法可以实现？ |
|
p*****g
发帖数: 445 | 40 我在code里面用了typedef定义以下
typedef state_t state_component[FC_MODES][TANK_LEVELS][BATT_LEVELS];
然后我用new新建两个指针
state_component* table1 = new state_component;
state_component* table2 = new state_component;
用vc++ 2005complie的时候报错
Error 30 error C2440: 'initializing' : cannot convert from 'state_t (*
)[256][256]' to 'state_component (*)'
但是当我把两个指针这样建立：
state_component* table1 = new state_component[1];
state_component* table2 = new state_component[1];
compile的时候就通过了。
望大牛出来传道授业解惑啊！ |
|
l******9
发帖数: 579 | 41 【 以下文字转载自 JobHunting 讨论区 】
发信人: light009 (light009), 信区: JobHunting
标 题: error of sql query in MS Access database
发信站: BBS 未名空间站 (Wed Oct 15 17:38:15 2014, 美东)
I need to do a sql query in MS Access 2012.
But I got error in MS Access:
SELECT *
FROM
(
SELECT *
FROM table1
where not exists
(
SELECT *
FROM table2
where table2.id = table1.id
) as t
) as t1, table3
where table3.id = t1.id
Syntax error: (missing operator) in query expression 'not exists ( ... ) as
t'
A... 阅读全帖 |
|
n*w
发帖数: 3393 | 42 没必要套这么多层。
SELECT *
FROM
(
SELECT *
FROM table1
where not exists
(
SELECT *
FROM table2
where table2.id = table1.id
)
) as t1, table3
where table3.id = t1.id |
|
c*********e
发帖数: 16335 | 43 (select col1,col2,col3 from table1
except
select col1,col2,col3 from table2)
union
(select col1,col2,col3 from table2
except
select col1,col2,col3 from table1) |
|
l******9
发帖数: 579 | 44 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: sort two same tables SQL but different results
发信站: BBS 未名空间站 (Fri May 9 09:57:41 2014, 美东)
I am sorting two tables on SQL.
The two tables have the same column names and types and rows numbers.
I used order by to do sorting but the two tables are different in order.
Example,
SELECT *
FROM table1 AS t1
ORDER BY t1.col1 , t1.col2, t1.col3, t1.col4 ASC
SELECT *
FROM table2 AS t2
ORDER BY t2.col1 , t2.col2, t2.col3, t2.col4 ASC
T... 阅读全帖 |
|
z*****h
发帖数: 17 | 45 data table2(drop=i temp);
set table2;
array cat categories_A categories_B categories_C;
do i=1 to dim(cat);
value=cat[i];
call vname(cat[i],temp);
categories=substr(temp,12,1);
output;
end;
run; |
|
k**g
发帖数: 1558 | 46 Thanks! If I have two existing tables, is this correct?
Proc SQL;
Create index Table1 date;
Create index Table2 date;
Create Table Table3 As
Select a.*, b.*
From Table1 a
Join Table2 b
On a.date=b.date; |
|
p********r
发帖数: 1465 | 47 我是用的
data table1 table2;
set table;
if uniform(0) < .8 then output table1;
else output table2;
run;
不过我稍微试了几次，发觉一个问题：不是每次分组的数目是一样的。比如table里总
共有100个，第一次分，table1里有82个，下一次可能就成了80个…… |
|
p*******r
发帖数: 1951 | 48 table1是原始数据，table2是变量说明。
data _null_;
set table2 end=eof;
length temp $200.;
if type = 1 then temp=catx(" ",temp,var);
retain temp;
if eof then call symput('vars', temp);
run;
data pick(keep=id &vars);
set table1;
run; |
|
i******r
发帖数: 861 | 49 搞定了。用了3个Proc sql，先得到table1（ID,m1），再得到table2（ID,m2），最后
合并table1/table2。
有更好的办法吗？ |
|
l******9
发帖数: 579 | 50 【 以下文字转载自 Database 讨论区 】
发信人: light009 (light009), 信区: Database
标 题: sort two same tables SQL but different results
发信站: BBS 未名空间站 (Fri May 9 09:57:41 2014, 美东)
I am sorting two tables on SQL.
The two tables have the same column names and types and rows numbers.
I used order by to do sorting but the two tables are different in order.
Example,
SELECT *
FROM table1 AS t1
ORDER BY t1.col1 , t1.col2, t1.col3, t1.col4 ASC
SELECT *
FROM table2 AS t2
ORDER BY t2.col1 , t2.col2, t2.col3, t2.col4 ASC
T... 阅读全帖 |
|
