A表 B表
NO Name amount num NO Name amount num
001 GP 20 1 001 GP 20 1
001 GP 40 3 001 GP 40 1
001 GP 45 1 001 GP 40 1
001 HQ 40 2 001 HC 20 1
002 GP 20 1 002 GP 40 1
002 HQ 40 2 002 HQ 40 1
002 HQ 40 1怎么通过查询语句得到下面的表;
NO Name amount num Name1 amount1 num1
001 GP 40 3 GP 40 2
001 GP 45 1 null null null
001 HQ 40 2 null null null
001 null null null HC 20 1
002 GP 20 1 NULL NULL NULL
002 NULL NULL NULL GP 40 1
帮忙看看谢谢!A表与B表是多对多的关系!能否有办法处理?a表,B表都要GROUP by NO,Name,amount下得到SUM(num)
NO Name amount num NO Name amount num
001 GP 20 1 001 GP 20 1
001 GP 40 3 001 GP 40 1
001 GP 45 1 001 GP 40 1
001 HQ 40 2 001 HC 20 1
002 GP 20 1 002 GP 40 1
002 HQ 40 2 002 HQ 40 1
002 HQ 40 1怎么通过查询语句得到下面的表;
NO Name amount num Name1 amount1 num1
001 GP 40 3 GP 40 2
001 GP 45 1 null null null
001 HQ 40 2 null null null
001 null null null HC 20 1
002 GP 20 1 NULL NULL NULL
002 NULL NULL NULL GP 40 1
帮忙看看谢谢!A表与B表是多对多的关系!能否有办法处理?a表,B表都要GROUP by NO,Name,amount下得到SUM(num)
001 GP 40 3 GP 40 2 ---GP 40 2 怎么来的?
(
select no ,name, amount , sum(num) num
from a group by no , name ,amount
) m where not exists(select 1 from
(
select no , Name , amount , sum(num) num
from b group by no , name ,amount
) n
where no = m.no and name = m.name and amount = m.amount
)
union all
select null no1 , null Name1 , null amount1 , null num1 , no no2 , Name Name2, amount amount2, num num2 from
(
select no , name, amount , sum(num) num
from b group by no , name ,amount
) m where not exists(select 1 from
(
select no , Name , amount , sum(num) num
from a group by no , name ,amount
) n
where no = m.no , name = m.name and amount = m.amount
)
insert into [A] values('001' , 'GP' , 20 , 1)
insert into [A] values('001' , 'GP' , 40 , 3)
insert into [A] values('001' , 'GP' , 45 , 1)
insert into [A] values('001' , 'HQ' , 40 , 2)
insert into [A] values('002' , 'GP' , 20 , 1)
insert into [A] values('002' , 'HQ' , 40 , 2 )create table [B](no varchar(3) , [Name] varchar(2),[amount] int,[num] int)
insert into [B] values('001' , 'GP' , 20 , 1)
insert into [B] values('001' , 'GP' , 40 , 1)
insert into [B] values('001' , 'GP' , 40 , 1)
insert into [B] values('001' , 'HC' , 20 , 1)
insert into [B] values('002' , 'GP' , 40 , 1)
insert into [B] values('002' , 'HQ' , 40 , 1)
insert into [B] values('002' , 'HQ' , 40 , 1)
select NO no1 , Name Name1, amount amount1, num num1 , null no2 , null Name2 , null amount2 , null num2 from
(
select no ,name, amount , sum(num) num
from a group by no , name ,amount
) m where not exists(select 1 from
(
select no , Name , amount , sum(num) num
from b group by no , name ,amount
) n
where no = m.no and name = m.name and amount = m.amount
)
union all
select null no1 , null Name1 , null amount1 , null num1 , no no2 , Name Name2, amount amount2, num num2 from
(
select no , name, amount , sum(num) num
from b group by no , name ,amount
) m where not exists(select 1 from
(
select no , Name , amount , sum(num) num
from a group by no , name ,amount
) n
where no = m.no AND name = m.name and amount = m.amount
)drop table a , b/*
no1 Name1 amount1 num1 no2 Name2 amount2 num2
---- ----- ----------- ----------- ---- ----- ----------- -----------
001 GP 45 1 NULL NULL NULL NULL
001 HQ 40 2 NULL NULL NULL NULL
002 GP 20 1 NULL NULL NULL NULL
NULL NULL NULL NULL 001 HC 20 1
NULL NULL NULL NULL 002 GP 40 1(所影响的行数为 5 行)
*/
首先谢谢您,好像不对,
001 GP 40 3 GP 40 2
这条的记录没取到!
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date : 2009-12-07 15:31:00
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: @ta
declare @ta table (NO varchar(3),Name varchar(2),amount int,num int)
insert into @ta
select '001','GP',20,1 union all
select '001','GP',40,3 union all
select '001','GP',45,1 union all
select '001','HQ',40,2 union all
select '002','GP',20,1 union all
select '002','HQ',40,2
--> 测试数据: @tb
declare @tb table (NO varchar(3),Name varchar(2),amount int,num int)
insert into @tb
select '001','GP',20,1 union all
select '001','GP',40,1 union all
select '001','GP',40,1 union all
select '001','HC',20,1 union all
select '002','GP',40,1 union all
select '002','HQ',40,1 union all
select '002','HQ',40,1select * from (
select a.no,a.name,a.amount,a.num,
name1=b.name,amount1=b.amount,num1=b.num
from (
select no ,name,amount,num=sum(num) from @ta group by no ,name,amount
)a
full join
(select no ,name,amount,num=sum(num) from @tb group by no ,name,amount
)b
on a.no=b.no and a.name=b.name and a.amount=b.amount --and a.num =b.num
) t
where isnull(name,'')!=isnull(name1,'') or isnull(amount,0)!=isnull(amount1,0) or isnull(num,0)!= isnull(num1,0)
no name amount num name1 amount1 num1
---- ---- ----------- ----------- ----- ----------- -----------
001 GP 40 3 GP 40 2
001 GP 45 1 NULL NULL NULL
NULL NULL NULL NULL HC 20 1
001 HQ 40 2 NULL NULL NULL
002 GP 20 1 NULL NULL NULL
NULL NULL NULL NULL GP 40 1(6 行受影响)
还是
要NO,Name,amount,sum(num)都不相同?create table [A](no varchar(3),[Name] varchar(2),[amount] int,[num] int)
insert into [A] values('001' , 'GP' , 20 , 1)
insert into [A] values('001' , 'GP' , 40 , 3)
insert into [A] values('001' , 'GP' , 45 , 1)
insert into [A] values('001' , 'HQ' , 40 , 2)
insert into [A] values('002' , 'GP' , 20 , 1)
insert into [A] values('002' , 'HQ' , 40 , 2 )create table [B](no varchar(3) , [Name] varchar(2),[amount] int,[num] int)
insert into [B] values('001' , 'GP' , 20 , 1)
insert into [B] values('001' , 'GP' , 40 , 1)
insert into [B] values('001' , 'GP' , 40 , 1)
insert into [B] values('001' , 'HC' , 20 , 1)
insert into [B] values('002' , 'GP' , 40 , 1)
insert into [B] values('002' , 'HQ' , 40 , 1)
insert into [B] values('002' , 'HQ' , 40 , 1)select isnull(t1.no , t2.no) no,
t1.Name,t1.amount,t1.num,
t2.Name,t2.amount,t2.num
from
(
select m.* from
(
select no ,name, amount , sum(num) num
from a group by no , name ,amount
) m where not exists(select 1 from
(
select no , Name , amount , sum(num) num
from b group by no , name ,amount
) n
where no = m.no and name = m.name and amount = m.amount and num = m.num
)
)t1
full join
(
select m.* from
(
select no , name, amount , sum(num) num
from b group by no , name ,amount
) m where not exists(select 1 from
(
select no , Name , amount , sum(num) num
from a group by no , name ,amount
) n
where no = m.no AND name = m.name and amount = m.amount and num = m.num
)
) t2
on t1.no = t2.no and t1.name = t2.name and t1.amount = t2.amount
order by t1.no , case when t1.name is not null then 1 else 2 end , t1.amount , t1.numdrop table a , b/*
no Name amount num Name amount num
---- ---- ----------- ----------- ---- ----------- -----------
001 HQ 40 2 NULL NULL NULL
001 GP 40 3 GP 40 2
001 GP 45 1 NULL NULL NULL
001 NULL NULL NULL HC 20 1
002 GP 20 1 NULL NULL NULL
002 NULL NULL NULL GP 40 1(所影响的行数为 6 行)
*/
declare @ta table(no varchar(10),name varchar(10),amount int ,num int)
insert into @ta
select '001','GP',20,1 union all
select '001','GP',40,3 union all
select '001','GP',45,1 union all
select '001','HQ',40,2 union all
select '002','GP',20,1 union all
select '002','HQ',40,2 declare @tb table(no varchar(10),name varchar(10),amount int ,num int)
insert into @tb
select '001','GP',20,1 union all
select '001','GP',40,1 union all
select '001','GP',40,1 union all
select '001','HC',20,1 union all
select '002','GP',40,1 union all
select '002','HQ',40,1 union all
select '002','HQ',40,1
--SQL语句
select no=COALESCE(a.no,b.no) ,a.name,a.amount,a.num,b.name,b.amount,b.num from @ta a
full join (
select no,name,amount,num=count(1) from @tb group by no,name,amount
)b
on (a.no=b.no and a.name=b.name and a.amount=b.amount )
where isnull(a.num,0)<>isnull(b.num,0)
order by no/*
no name amount num name amount num
---------- ---------- ----------- ----------- ---------- ----------- -----------
001 GP 40 3 GP 40 2
001 GP 45 1 NULL NULL NULL
001 HQ 40 2 NULL NULL NULL
001 NULL NULL NULL HC 20 1
002 NULL NULL NULL GP 40 1
002 GP 20 1 NULL NULL NULL(6 行受影响)
*/
create table A表 ([NO] varchar(4),[Name] varchar(4),amount int,num int)
insert A表 select '001','GP','20','1'
union all select '001','GP','40','3'
union all select '001','GP','45','1'
union all select '001','HQ','40','2'
union all select '002','GP','20','1'
union all select '002','HQ','40','2'
create table B表 ([NO] varchar(4),[Name] varchar(4),amount int,num int)
insert B表 select '001','GP','20','1'
union all select '001','GP ','40','1'
union all select '001','GP','40','1'
union all select '001','HC ','20','1'
union all select '002','GP','40','1'
union all select '002','HQ ','40','1'
union all select '002','HQ','40','1'
select * from
(select t.[NO],t.[Name],t.amount,sum(num) as num from A表 t group by t.[NO],t.[Name],t.amount) as a
full join (select t.[NO],t.[Name],t.amount,sum(num) as num from B表 t group by t.[NO],t.[Name],t.amount) as b on
checksum(a.[NO],a.[Name],a.amount)=checksum(b.[NO],b.[Name],b.amount)
where checksum(a.[NO],a.[Name],a.amount,a.num)<>checksum(b.[NO],b.[Name],b.amount,b.num)/*
(6 行受影响)(7 行受影响)
NO Name amount num NO Name amount num
---- ---- ----------- ----------- ---- ---- ----------- -----------
001 GP 40 3 001 GP 40 2
NULL NULL NULL NULL 001 HC 20 1
NULL NULL NULL NULL 002 GP 40 1
001 HQ 40 2 NULL NULL NULL NULL
001 GP 45 1 NULL NULL NULL NULL
002 GP 20 1 NULL NULL NULL NULL(6 行受影响)*/
--> 测试数据:[A表]
if object_id('[A表]') is not null drop table [A表]
create table [A表]([NO] varchar(3),[Name] varchar(2),[amount] int,[num] int)
insert [A表]
select '001','GP',20,1 union all
select '001','GP',40,3 union all
select '001','GP',45,1 union all
select '001','HQ',40,2 union all
select '002','GP',20,1 union all
select '002','HQ',40,2
--> 测试数据:[B表]
if object_id('[B表]') is not null drop table [B表]
create table [B表]([NO] varchar(3),[Name] varchar(2),[amount] int,[num] int)
insert [B表]
select '001','GP',20,1 union all
select '001','GP',40,1 union all
select '001','GP',40,1 union all
select '001','HC',20,1 union all
select '002','GP',40,1 union all
select '002','HQ',40,1 union all
select '002','HQ',40,1select * from
(
select * ,
(select max(Name) from [B表] where [NO] = t.[NO] and [Name] = t.[Name] and [amount] = t.[amount]) as Name1,
(select max([amount]) from [B表] where [NO] = t.[NO] and [Name] = t.[Name] and [amount] = t.[amount]) as [amount1],
(select sum([num]) from [B表] where [NO] = t.[NO] and [Name] = t.[Name] and [amount] = t.[amount]) as [num1]
from [A表] t
union
select r.[NO],
(select max(Name) from [A表] where [NO] = r.[NO] and [Name] = r.[Name] and [amount] = r.[amount]) as Name,
(select max([amount]) from [A表] where [NO] = r.[NO] and [Name] = r.[Name] and [amount] = r.[amount]) as [amount],
(select sum([num]) from [A表] where [NO] = r.[NO] and [Name] = r.[Name] and [amount] = r.[amount]) as [num],
r.Name as Name1,r.[amount] as [amount1],r.[num] as [num1]
from (select no,name,amount,sum(num) as num from [B表] group by no,name,amount) r
) h
where isnull(num,0) <> isnull(num1,0)
order by NO
--结果
-----------------------------------
001 NULL NULL NULL HC 20 1
001 GP 40 3 GP 40 2
001 GP 45 1 NULL NULL NULL
001 HQ 40 2 NULL NULL NULL
002 NULL NULL NULL GP 40 1
002 GP 20 1 NULL NULL NULL
declare @ta table(no varchar(10),name varchar(10),amount int ,num int)
insert into @ta
select '001','GP',20,1 union all
select '001','GP',40,3 union all
select '001','GP',45,1 union all
select '001','HQ',40,2 union all
select '002','GP',20,1 union all
select '002','HQ',40,2 declare @tb table(no varchar(10),name varchar(10),amount int ,num int)
insert into @tb
select '001','GP',20,1 union all
select '001','GP',40,1 union all
select '001','GP',40,1 union all
select '001','HC',20,1 union all
select '002','GP',40,1 union all
select '002','HQ',40,1 union all
select '002','HQ',40,1 select ta.no,ta.name,ta.amount,ta.num,tb.name,tb.amount,tb.num from
(select no,name,amount,sum(num) as num from @ta group by no,name,amount,num) as ta
full join
(select no,name,amount,sum(num) as num from @tb group by no,name,amount,num ) as tb
on ta.no=tb.no and ta.name=tb.name and ta.amount=tb.amount
--------------------------------------
结果
001 GP 20 1 GP 20 1
001 GP 40 3 GP 40 2
001 GP 45 1 NULL NULL NULL
NULL NULL NULL NULL HC 20 1
001 HQ 40 2 NULL NULL NULL
002 GP 20 1 NULL NULL NULL
NULL NULL NULL NULL GP 40 1
002 HQ 40 2 HQ 40 2
select ta.no,ta.name,ta.amount,ta.num,tb.name,tb.amount,tb.num from
(select no,name,amount,sum(num) as num from @ta group by no,name,amount,num) as ta
full join
(select no,name,amount,sum(num) as num from @tb group by no,name,amount,num ) as tb
on ta.no=tb.no and ta.name=tb.name and ta.amount=tb.amount where isnull(ta.num,0)<>isnull(tb.num,0)补充一下