求复杂一点的SQL语句

Question

表USER
USERID USERNAME BUMEN
1       TOM     SC
2       JIM      SC
3       MARRY    CW
4       lily     CW
表DDJDATA
USERNAME DDJ
TOM      DDJA
TOM      DDJB
表XWBDATA
USERNAME  XWB
TOM       XWBA
MARRY     XWBB
表KHGHDATA
USERNAME  KHGH
MARRY      KHGH1
JIM        KHGH2      
求统计功能得到下表的一SQL语句
USERNAME DDJ XWB KHGH
TOM       2   1   0
MARRY     0   0   1
JIM       0   0   1
lily      0   0   0
以及下表的语句
bumen    ddj xwb khgh
sc        2   1  1
cw        0   1  1
谢谢啊

Answer 1

--1、select 
     USERNAME ,
     (select count(*) from DDJDATA where USERNAME =A.USERNAME ) as DDJ ,
     (select count(*) from DDJDATA where USERNAME =A.USERNAME ) as XWB ,
     (select count(*) from KHGHDATA where USERNAME =A.USERNAME ) as KHGH
from [USER] as A
--2、select 
     C.BUMEN,
     sum(B.DDJ) AS DDJ,
     sum(B.XWB) AS XWB,
     sum(B.KHGH) AS KHGH
from
(
select 
     USERNAME ,
     (select count(*) from DDJDATA where USERNAME =A.USERNAME ) as DDJ ,
     (select count(*) from DDJDATA where USERNAME =A.USERNAME ) as XWB ,
     (select count(*) from KHGHDATA where USERNAME =A.USERNAME ) as KHGH
from [USER] as A
) B 
    inner  join [USER] C on B.USERNAME=C.USERNAME
group by C.BUMEN

Answer 2

if object_id('pubs..[USER]') is not null
   drop table [USER]
gocreate table [USER](USERID int,USERNAME varchar(10),BUMEN varchar(10))
insert into [USER](USERID,USERNAME,BUMEN) values(1,       'TOM'  ,    'SC')
insert into [USER](USERID,USERNAME,BUMEN) values(2,       'JIM'  ,    'SC')
insert into [USER](USERID,USERNAME,BUMEN) values(3,       'MARRY',    'CW')
insert into [USER](USERID,USERNAME,BUMEN) values(4,       'lily' ,    'CW')
goif object_id('pubs..DDJDATA') is not null
   drop table DDJDATA
go
create table DDJDATA(USERNAME varchar(10),DDJ varchar(10))
insert into DDJDATA(USERNAME,DDJ) values('TOM',      'DDJA')
insert into DDJDATA(USERNAME,DDJ) values('TOM',      'DDJB')
goif object_id('pubs..XWBDATA') is not null
   drop table XWBDATA
go
create table XWBDATA(USERNAME varchar(10),XWB varchar(10))
insert into XWBDATA(USERNAME,XWB) values('TOM'  ,     'XWBA')
insert into XWBDATA(USERNAME,XWB) values('MARRY',     'XWBB')
goif object_id('pubs..KHGHDATA') is not null
   drop table KHGHDATA
gocreate table KHGHDATA(USERNAME varchar(10),KHGH varchar(10))
insert into KHGHDATA(USERNAME,KHGH) values('MARRY',      'KHGH1')
insert into KHGHDATA(USERNAME,KHGH) values('JIM'  ,      'KHGH2')
goselect distinct t.bumen ,isnull(t1.ddj,0) ddj , isnull(t2.xwb,0) xwb , isnull(t3.khgh,0) khgh from [user] t
left join (select a.bumen , count(*) ddj from [USER] a,DDJDATA b where a.username = b.username group by a.bumen) t1 on t.bumen = t1.bumen
left join (select a.bumen , count(*) xwb from [USER] a,XWBDATA b where a.username = b.username group by a.bumen) t2 on t.bumen = t2.bumen
left join (select a.bumen , count(*) khgh from [USER] a,KHGHDATA b where a.username = b.username group by a.bumen) t3 on t.bumen = t3.bumen
order by t.bumen descdrop table [USER],DDJDATA,XWBDATA,KHGHDATA/*
bumen      ddj         xwb         khgh        
---------- ----------- ----------- ----------- 
SC         2           1           1
CW         0           1           1（所影响的行数为 2 行）
*/

Answer 3

if object_id('pubs..[USER]') is not null
   drop table [USER]
gocreate table [USER](USERID int,USERNAME varchar(10),BUMEN varchar(10))
insert into [USER](USERID,USERNAME,BUMEN) values(1,       'TOM'  ,    'SC')
insert into [USER](USERID,USERNAME,BUMEN) values(2,       'JIM'  ,    'SC')
insert into [USER](USERID,USERNAME,BUMEN) values(3,       'MARRY',    'CW')
insert into [USER](USERID,USERNAME,BUMEN) values(4,       'lily' ,    'CW')
goif object_id('pubs..DDJDATA') is not null
   drop table DDJDATA
go
create table DDJDATA(USERNAME varchar(10),DDJ varchar(10))
insert into DDJDATA(USERNAME,DDJ) values('TOM',      'DDJA')
insert into DDJDATA(USERNAME,DDJ) values('TOM',      'DDJB')
goif object_id('pubs..XWBDATA') is not null
   drop table XWBDATA
go
create table XWBDATA(USERNAME varchar(10),XWB varchar(10))
insert into XWBDATA(USERNAME,XWB) values('TOM'  ,     'XWBA')
insert into XWBDATA(USERNAME,XWB) values('MARRY',     'XWBB')
goif object_id('pubs..KHGHDATA') is not null
   drop table KHGHDATA
gocreate table KHGHDATA(USERNAME varchar(10),KHGH varchar(10))
insert into KHGHDATA(USERNAME,KHGH) values('MARRY',      'KHGH1')
insert into KHGHDATA(USERNAME,KHGH) values('JIM'  ,      'KHGH2')
goselect t.username ,isnull(t1.ddj,0) ddj , isnull(t2.xwb,0) xwb , isnull(t3.khgh,0) khgh from [user] t
left join (select username , count(*) ddj from DDJDATA group by username) t1 on t.username = t1.username
left join (select username , count(*) xwb from XWBDATA group by username) t2 on t.username = t2.username
left join (select username , count(*) KHGH from KHGHDATA group by username) t3 on t.username = t3.username
order by t.usernamedrop table [USER],DDJDATA,XWBDATA,KHGHDATA/*
username   ddj         xwb         khgh        
---------- ----------- ----------- ----------- 
JIM        0           0           1
lily       0           0           0
MARRY      0           1           1
TOM        2           1           0（所影响的行数为 4 行）
*/

Answer 4

declare @表USER table(USERID INT, USERNAME VARCHAR(10),BUMEN VARCHAR(10))
INSERT INTO @表USER SELECT 1,'TOM','SC'
UNION ALL SELECT 2,'JIM','SC'
UNION ALL SELECT 3,'MARRY','CW'
UNION ALL SELECT 4,'LILY','CW'DECLARE @表DDJDATA TABLE(USERNAME VARCHAR(10),DDJ VARCHAR(10))
INSERT INTO @表DDJDATA SELECT 'TOM','DDJA'
UNION ALL SELECT 'TOM','DDJB'DECLARE @表XWBDATA TABLE(USERNAME VARCHAR(10),XWB VARCHAR(10))
INSERT INTO @表XWBDATA SELECT 'TOM','XWBA'
UNION ALL SELECT 'MARRY','XWBB'DECLARE @表KHGHDATA TABLE(USERNAME VARCHAR(10),KHGH VARCHAR(10))
INSERT INTO @表KHGHDATA SELECT 'MARRY','KHGH1'
UNION ALL SELECT 'JIM','KHGH2'SELECT A.USERNAME,DDJ=(SELECT COUNT(1) FROM @表DDJDATA WHERE USERNAME=A.USERNAME),
XWB=(SELECT COUNT(1) FROM @表XWBDATA WHERE USERNAME=A.USERNAME),
KHGH=(SELECT COUNT(1) FROM @表KHGHDATA WHERE USERNAME=A.USERNAME)
FROM @表USER A

SELECT BUMEN,DDJ=SUM(TB.DDJ),
xwb=SUM(TB.xwb),
khgh=SUM(TB.khgh)
FROM
(
SELECT bumen=BUMEN,
       ddj=(select count(DDJ) from  @表DDJDATA where USERNAME=a.USERNAME),
       xwb=(select count(XWB) from  @表XWBDATA where USERNAME=a.USERNAME),
       khgh=(select count(KHGH) from  @表KHGHDATA where USERNAME=a.USERNAME)
from @表USER a
) TB
GROUP BY TB.BUMEN
/*
（所影响的行数为 4 行）
（所影响的行数为 2 行）
（所影响的行数为 2 行）
（所影响的行数为 2 行）USERNAME   DDJ         XWB         KHGH        
---------- ----------- ----------- ----------- 
TOM        2           1           0
JIM        0           0           1
MARRY      0           1           1
LILY       0           0           0（所影响的行数为 4 行）BUMEN      DDJ         xwb         khgh        
---------- ----------- ----------- ----------- 
CW         0           1           1
SC         2           1           1（所影响的行数为 2 行）*/

Answer 5

similarly answer below:HTTP://www.ebigear.com/Fund/PlayNews.php?NewsID=28340&ID=285885

Answer 6

create table [user](userid int identity(1,1) ,username varchar(20),bumen varchar(20))
insert into [user](username,bumen) select 'tom','sc'
union all select 'jim','sc'
union all select 'marry','cw'
union all select 'lily','cw'
create table ddjdata (username varchar(20),ddj varchar(20))
insert into ddjdata select 'tom','ddja'
insert into ddjdata select 'tom','ddjb'create table xwbdata (username varchar(20),xwb varchar(20))
insert into xwbdata select 'tom','xwba'
insert into xwbdata select 'marry','xwbb'
create table khghdata (username varchar(20),khgh varchar(20))
insert into khghdata select 'marry','hkgh1'
insert into khghdata select 'jim','hkgh1'select username,
(select count(1) from ddjdata where username=a.username) as ddj,
(select count(1) from xwbdata where username=a.username) as xwb,
(select count(1) from khghdata where username=a.username) as khgh
from [user] a 
username             ddj         xwb         khgh        
-------------------- ----------- ----------- ----------- 
tom                  2           1           0
jim                  0           0           1
marry                0           1           1
lily                 0           0           0（所影响的行数为 4 行）select bumen,sum(ddj) as ddj,sum(xwb) as xwb,sum(khgh) as khgh from 
(select bumen,
(select count(1) from ddjdata where username=a.username) as ddj,
(select count(1) from xwbdata where username=a.username) as xwb,
(select count(1) from khghdata where username=a.username) as khgh
from [user] a) b 
group by bumenbumen                ddj         xwb         khgh        
-------------------- ----------- ----------- ----------- 
cw                   0           1           1
sc                   2           1           1（所影响的行数为 2 行）