如何写这个SQL语句？(100分)

F

fstao

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-08

• #1

sql server 7.0的问题：
有一个表dbo.table1(字段sl为数量)，数据如下：
name sl
A 3
B 4
A 2

另一个表dbo.table2(字段sl为数量)，数据如下：
name sl
A 2
B 3
A 1
A 1
B 1

dbo.table1的货品A的数量一共为5件，B一共为4件，dbo.table2的货品
A的数量一共为4件，B一共为4件，如何用select count(*) 来查找dbo.table1的
货品的数量与dbo.table2的货品的数量有多少个货品不相等？
例如上面的例子，数量不相等只有一个货品A。
如果dbo.tabl2的数据为：
name sl
A 2
B 1
A 1
A 1
B 1

这样就有2个货品(A和B货品)的数量不相等。
请问如何写select count(*) 的语句？

 

J

jqw

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-08

• #2

Query1:
select name,sum(s1) from table1
group by name;

Query2:
select name,sum(s1) from table2
group by name;

将query1和query2进行比较

or:
select name,sum(s1) from table1
group by name
minus
select name,sum(s1) from table2
group by name;

 

H

hhzh426

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-08

• #3

有意思！

 

L

lazy_cat

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-08

• #4

minus 不能在sqlserver 中用
應這樣寫
select name,sum(s1)
from table1
where exists (select name,sum(s1) from table2 group by name)
group by name
union
select name,sum(s1)
from table2
where exists (select name,sum(s1) from table1 group by name)
group by name
沒有經過測試,大概如此吧

 

I

IWTBAH

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-08

• #5

我想可以这样写：
select count(*) from
(select name,sum(sl) from table1
group by name
having sum(sl)<>(select sum(sl) from table2
where name=table1.name
)
)

 

S

soho

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-09

• #6

select count(*) from
(select name sum(sl) from dbo.table1 t1
group by name
having sum(sl)<>(select sum(sl) from dbo.table2 t2
where name=t1.name )
)

 

Z

zs

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-09

• #7

select t1.name,count(*) as sl
from
select name,sum(sl) as sl
from dbo.table1
group by name ) t1,
select name,sum(sl) as sl
from dbo.table2
group by name ) t2
where t1.name=t2.name and t1.sl<>t2.sl
group by t1.name;
只是，table2中的记录table1中必须存在。

 

L

LGXing

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-10

• #8

Select t1.name,sum(t1.s1) as s1,sum(t2.s1) as s2 from t1,t2 where s1<>s2

???RecordCount

只是，table2中的货品种类与table1中必须一样多.否则:

Select t1.name as name1,t2.name as name2,sum(t1.s1) as s1,sum(t2.s1) as s2
from t1,t2
where s1<>s2

我也迷了,思路如此

 

W

wumeng

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-10

• #9

用笨一点的方法吧。－－＞建一个ＴＡＢＬＥ作为汇总表。

 

F

fstao

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2000-10-13

• #10

多人接受答案了。