1.现在A、B表两表查询后如下所示: A.docnum A.cardcode A.cardname B.docnum B.cardcode B.cardname
1 LB 李白 23 ND 牛吨
1 LB 李白 24 ND 牛吨
2. 如何实现A、B表两表查询后得到如下结果:(A表少了一条记录) A.docnum A.cardcode A.cardname B.docnum B.cardcode B.cardname
1 LB 李白 23 ND 牛吨
24 ND 牛吨附:不可用UNION ALL 来实现,求解!谢谢!
1 LB 李白 23 ND 牛吨
1 LB 李白 24 ND 牛吨
2. 如何实现A、B表两表查询后得到如下结果:(A表少了一条记录) A.docnum A.cardcode A.cardname B.docnum B.cardcode B.cardname
1 LB 李白 23 ND 牛吨
24 ND 牛吨附:不可用UNION ALL 来实现,求解!谢谢!
如果记录重复出现就替换为空,如果没有就显示
比如
序号 货物 数量
1 A 30
2 10
3 -5
4 B 20
5 100
6 C 1
7 98declare @t table(序号 int,货物 varchar(20),数量 int)
insert into @t
select 1 , 'A' , 30 union
select 2 , 'a' , 10 union
select 3 , 'a' , -5 union
select 4 , 'B' , 20 union
select 5 , 'b' , 100 union
select 6 , 'C' , 1 union
select 7 , 'c' , 98 select 序号,case when 序号=(select min(序号) from @t where 货物=a.货物) then 货物 else '' end as 货物, 数量
from @t a
order by 序号/*
序号 货物 数量
----------- -------------------- -----------
1 A 30
2 10
3 -5
4 B 20
5 100
6 C 1
7 98(所影响的行数为 7 行)
*/
declare @B table (docnum int,cardcode varchar(2),cardname varchar(4))
insert into @B
select 23,'ND','牛吨' union all
select 24,'ND','牛吨'select DISTINCT * from @A a right JOIN @B b ON a.docnum=b.docnum-22
ORDER BY a.docnum DESC
/*
docnum cardcode cardname docnum cardcode cardname
----------- -------- -------- ----------- -------- --------
1 LB 李白 23 ND 牛吨
NULL NULL NULL 24 ND 牛吨
*/
declare @A table (docnum int,cardcode varchar(2),cardname varchar(4))
insert into @A
select 1,'LB','李白' union all
select 1,'LB','李白'declare @B table (docnum int,cardcode varchar(2),cardname varchar(4))
insert into @B
select 23,'ND','牛吨' union all
select 24,'ND','牛吨'select DISTINCT * from @A a right JOIN @B b ON a.docnum=b.docnum-22
ORDER BY a.docnum DESC
/*
docnum cardcode cardname docnum cardcode cardname
----------- -------- -------- ----------- -------- --------
1 LB 李白 23 ND 牛吨
NULL NULL NULL 24 ND 牛吨
*/刚才没发全..
case sn when 1 then A_docnum end,
case sn when 1 then A_cardcode end,
case sn when 1 then A_cardname end,
B_docnum, B_cardcode, B_cardname
from
(
select sn=row_number() over(partition by A_docnum, A_cardcode, A_cardname), *
from
(select A.docnum as A_docnum,A.cardcode as A_cardcode,A.cardname as A_cardname,
B.docnum as B_docnum ,B.cardcode as B_cardcode ,B.cardname as B_cardname from A join B on 你的条件) t1
) t2
case sn when 1 then A_docnum end,
case sn when 1 then A_cardcode end,
case sn when 1 then A_cardname end,
B_docnum, B_cardcode, B_cardname
from
(
select sn=row_number() over(partition by A_docnum, A_cardcode, A_cardname
order by A_docnum, A_cardcode, A_cardname), *
from
(select A.docnum as A_docnum,A.cardcode as A_cardcode,A.cardname as A_cardname,
B.docnum as B_docnum ,B.cardcode as B_cardcode ,B.cardname as B_cardname from A join B on 你的条件) t1
) t2上面少了order by
use tempdb;
/*
create table A
(
docnum int not null,
cardcode nvarchar(10) not null,
cardname nvarchar(10) not null
);
insert into A(docnum,cardcode,cardname)
values
(1,'LB','李白');create table B
(
docnum int not null,
cardcode nvarchar(10) not null,
cardname nvarchar(10) not null
);
insert into B(docnum,cardcode,cardname)
values
(23,'ND','牛吨'),
(24,'ND','牛吨');
*/
select
case when sortnum <> 1 then null else t2.docmum1 end as [A.docnum],
case when sortnum <> 1 then null else t2.cardcode1 end as [A.cardcode],
case when sortnum <> 1 then null else t2.cardname1 end as [A.cardname],
t2.docmum2 as [B.docnum],
t2.cardcode2 as [B.cardcode],
t2.cardname2 as [B.cardname]
from
(
select *,
ROW_NUMBER() over(partition by docmum1,cardcode1,cardname1 order by docmum1,cardcode1,cardname1) as [sortnum]
from
(
select
A.docnum as docmum1,
A.cardcode as cardcode1,
A.cardname as cardname1,
B.docnum as docmum2,
B.cardcode as cardcode2,
B.cardname as cardname2
from A cross join B
) as t1
) as t2;