大家好,我有两个表A, B
表A:
USR DD TI_COUNT TI_KIND
F1 2008-11-29 0:00 2 1
F1 2008-11-29 0:00 1 2表B:
USR DD MC_COUNT MC_KIND
F1 2008-11-29 0:00 1 1我想实现连接表后得出的结果是:
USR DD TI_COUNT TI_KIND MC_COUNT MC_KIND
F1 2008-11-29 0:00 2 1 1 1
F1 2008-11-29 0:00 1 2 0 0但是我通过语句:
SELECT USR = CASE WHEN (ISNULL(A.USR, '') <> '') THEN A.USR ELSE B.USR END,
DD = CASE WHEN (ISNULL(A.TI_DD,'') <> '') THEN A.TI_DD ELSE B.MC_DD END,
ISNULL(A.TI_COUNT, 0) AS TI_COUNT,
ISNULL(A.TI_KIND, 0) AS TI_KIND,
ISNULL(B.MC_COUNT, 0) AS MC_COUNT,
ISNULL(B.MC_KIND, 0) AS MC_KIND
FROM A FULL JOIN B ON A.USR = B.USR AND A.DD = B.DD
得出的结果却是
USR DD TI_COUNT TI_KIND MC_COUNT MC_KIND
F1 2008-11-29 0:00 2 1 1 1
F1 2008-11-29 0:00 1 2 1 1请问大家,怎样连接两个表才能得出我要的结果呢?谢谢大家!
表A:
USR DD TI_COUNT TI_KIND
F1 2008-11-29 0:00 2 1
F1 2008-11-29 0:00 1 2表B:
USR DD MC_COUNT MC_KIND
F1 2008-11-29 0:00 1 1我想实现连接表后得出的结果是:
USR DD TI_COUNT TI_KIND MC_COUNT MC_KIND
F1 2008-11-29 0:00 2 1 1 1
F1 2008-11-29 0:00 1 2 0 0但是我通过语句:
SELECT USR = CASE WHEN (ISNULL(A.USR, '') <> '') THEN A.USR ELSE B.USR END,
DD = CASE WHEN (ISNULL(A.TI_DD,'') <> '') THEN A.TI_DD ELSE B.MC_DD END,
ISNULL(A.TI_COUNT, 0) AS TI_COUNT,
ISNULL(A.TI_KIND, 0) AS TI_KIND,
ISNULL(B.MC_COUNT, 0) AS MC_COUNT,
ISNULL(B.MC_KIND, 0) AS MC_KIND
FROM A FULL JOIN B ON A.USR = B.USR AND A.DD = B.DD
得出的结果却是
USR DD TI_COUNT TI_KIND MC_COUNT MC_KIND
F1 2008-11-29 0:00 2 1 1 1
F1 2008-11-29 0:00 1 2 1 1请问大家,怎样连接两个表才能得出我要的结果呢?谢谢大家!
insert into a values('F1', '2008-11-29' , 2 , 1 )
insert into a values('F1', '2008-11-29' , 1 , 2 )
create table b(USR varchar(10), DD datetime, MC_COUNT int, MC_KIND int)
insert into b values('F1', '2008-11-29' , 1 , 1 )
goselect a.* ,
case when TI_COUNT=(select max(TI_COUNT) from a where USR=a.USR) then b.mc_count else 0 end as mc_count,
case when TI_COUNT=(select max(TI_COUNT) from a where USR=a.USR) then b.MC_KIND else 0 end as MC_KIND
from a , b
where a.USR = b.USR and a.dd = b.dddrop table a , b/*
USR DD TI_COUNT TI_KIND mc_count MC_KIND
---------- ------------------------------------------------------ ----------- ----------- ----------- -----------
F1 2008-11-29 00:00:00.000 2 1 1 1
F1 2008-11-29 00:00:00.000 1 2 0 0(所影响的行数为 2 行)
*/
insert into a values('F1', '2008-11-29' , 2 , 1 )
insert into a values('F1', '2008-11-29' , 1 , 2 )
create table b(USR varchar(10), DD datetime, MC_COUNT int, MC_KIND int)
insert into b values('F1', '2008-11-29' , 1 , 1 )
goselect t.* ,
case when TI_COUNT=(select max(TI_COUNT) from a where USR = t.USR) then b.mc_count else 0 end as mc_count,
case when TI_COUNT=(select max(TI_COUNT) from a where USR = t.USR) then b.MC_KIND else 0 end as MC_KIND
from a t, b
where t.USR = b.USR and t.dd = b.dddrop table a , b/*
USR DD TI_COUNT TI_KIND mc_count MC_KIND
---------- ------------------------------------------------------ ----------- ----------- ----------- -----------
F1 2008-11-29 00:00:00.000 2 1 1 1
F1 2008-11-29 00:00:00.000 1 2 0 0(所影响的行数为 2 行)
*/
FROM 表1 LEFT JOIN 表2 ON (表1.TI_KIND = 表2.MC_KIND) AND (表1.DD = 表2.DD) AND (表1.USR = 表2.USR)
from a left join b on a.usr=b.usr and a.ti_kind=b.mc_countF1 2008-11-29 00:00:00.000 2 1 1 1
F1 2008-11-29 00:00:00.000 1 2 0 0
表A:
USR DD TI_COUNT TI_KIND
F1 2008-11-29 2 1
F1 2008-11-29 1 2
F1 2008-11-29 3 3
F2 2008-11-30 4 1表B:
USR DD MC_COUNT MC_KIND
F1 2008-11-29 3 1
F1 2008-11-29 4 2我想实现连接表后得出的结果是:
USR DD TI_COUNT TI_KIND MC_COUNT MC_KIND
F1 2008-11-29 2 1 3 1
F1 2008-11-29 1 2 4 2
F1 2008-11-29 3 3 0 0
F2 2008-11-30 4 1 0 0但是我通过语句:
SELECT USR = CASE WHEN (ISNULL(A.USR, '') <> '') THEN A.USR ELSE B.USR END,
DD = CASE WHEN (ISNULL(A.TI_DD,'') <> '') THEN A.TI_DD ELSE B.MC_DD END,
ISNULL(A.TI_COUNT, 0) AS TI_COUNT,
ISNULL(A.TI_KIND, 0) AS TI_KIND,
ISNULL(B.MC_COUNT, 0) AS MC_COUNT,
ISNULL(B.MC_KIND, 0) AS MC_KIND
FROM A FULL JOIN B ON A.USR = B.USR AND A.DD = B.DD 得出的结果却是
USR DD TI_COUNT TI_KIND MC_COUNT MC_KIND
F1 2008-11-29 2 1 3 1
F1 2008-11-29 2 1 4 2
F1 2008-11-29 1 2 3 1
F1 2008-11-29 1 2 4 2
F1 2008-11-29 3 3 3 1
F1 2008-11-29 3 3 4 2
F2 2008-11-30 4 1 0 0
请问应该如何实现?谢谢大家!
go
create table ta(USR varchar(5), DD datetime,TI_COUNT int,TI_KIND int)
insert ta select 'F1' , '2008-11-29' , 2 , 1
insert ta select 'F1' , '2008-11-29' , 1 , 2
insert ta select 'F1' , '2008-11-29' , 3 , 3
insert ta select 'F2' , '2008-11-30' , 4 , 1
if object_id('tb')is not null drop table tb
go
create table tb(USR varchar(5),DD datetime,MC_COUNT int,MC_KIND int)
insert tb select 'F1' , '2008-11-29', 3, 1
insert tb select 'F1' , '2008-11-29', 4, 2
alter table ta add id int identity
go
alter table tb add id int identity
go
select a.usr,a.dd,a.TI_COUNT,a.TI_KIND,isnull(b.MC_COUNT,0),isnull(b.MC_KIND,0) from ta a left join tb b on a.usr=b.usr and a.dd=b.dd and a.id=b.id
alter table ta drop column id
go
alter table tb drop column id
go
/*usr dd TI_COUNT TI_KIND
----- ------------------------------------------------------ ----------- ----------- ----------- -----------
F1 2008-11-29 00:00:00.000 2 1 3 1
F1 2008-11-29 00:00:00.000 1 2 4 2
F1 2008-11-29 00:00:00.000 3 3 0 0
F2 2008-11-30 00:00:00.000 4 1 0 0*/
USR DD TI_COUNT TI_KIND ID
F1 2008-11-28 1 1 1
F1 2008-11-29 2 1 2
F1 2008-11-29 1 2 3
F2 2008-11-28 1 2 1
SA 2008-9-27 1 1 1
SA 2008-10-6 1 1 2
select * , id = (select count(1) from tb where USR = t.USR and (dd<t.dd or (dd=t.dd and TI_COUNT < t.TI_COUNT ) ) ) + 1 from tb t
insert into tb values('F1' ,'2008-11-28', 1, 1)
insert into tb values('F1' ,'2008-11-29', 2, 1)
insert into tb values('F1' ,'2008-11-29', 1, 2)
insert into tb values('F2' ,'2008-11-28', 1, 2)
insert into tb values('SA' ,'2008-9-27' , 1, 1)
insert into tb values('SA' ,'2008-10-6' , 1, 1)
goselect * , id = (select count(1) from tb where USR = t.USR and (dd<t.dd or (dd=t.dd and TI_KIND < t.TI_KIND ) ) ) + 1 from tb t order by usr , iddrop table tb/*USR DD TI_COUNT TI_KIND id
---------- ------------------------------------------------------ ----------- ----------- -----------
F1 2008-11-28 00:00:00.000 1 1 1
F1 2008-11-29 00:00:00.000 2 1 2
F1 2008-11-29 00:00:00.000 1 2 3
F2 2008-11-28 00:00:00.000 1 2 1
SA 2008-09-27 00:00:00.000 1 1 1
SA 2008-10-06 00:00:00.000 1 1 2(所影响的行数为 6 行)
*/