超复杂的SQL,,,有兴趣的不妨看看^^

Question

这个可能看不清,,再多多次
Table4 
name color  Price MoneyUnit InAmount  InMoney  OutAmount  OutMoney  AtAmount  AtMoney 
001  red    1.2    RMB        2000      2400      800          60           1200      1440 
001  red    1.5    RMB        1000     1500                                 1000     1500 
002  blue  1.8    USD        1000      1800                                 1000      1800      
003  red    2.0    HKD        1000      2000      500         1000          500        1000 
003  red    1.2    HKD        1200      1440      200         240           1000      1200      
004  black  1.5    HKD        1800      2700                                1800      2700 
005  gray  1.2    RMB        2000       2400      900       1080            1100     1320       

Answer 1

select a.name,a.color,a.Price,a.MoneyUnit, sum(InAmount) InAmount, sum( InMoney)  InMoney
,  sum(OutAmount) OutAmount,sum(OutMoney) OutMoney, sum(AtAmount ) AtAmount,  sum(AtMoney )AtMoney
 from table1 a,table2 b, table3 c
where a.name=b.name
and a.color=b.color
and a.moneyunit=b.moneyunit
and a.price=b.price
and a.name=c.name
and a.color=c.color
and a.moneyunit=c.moneyunit
and a.price=c.price
group by name color Price MoneyUnit

Answer 2

select a.name, a.color, a.Price, a.MoneyUnit,sum(b.InAmount) InAmount,sum(b.InMoney) InMoney,
sum(c.OutAmount) OutAmount,sum(c.OutMoney) OutMoney,sum(a.AtAmount) AtAmount,sum(a.AtMoney) AtMoney
from Table1  a
left join table2 b on a.name=b.name and a.color=b.color and a.Price=b.Price and a.MoneyUnit=b.MoneyUnit
left join table3 c on a.name=c.name and a.color=c.color and a.Price=c.Price and a.MoneyUnit=c.MoneyUnit
group by a.name, a.color, a.Price, a.MoneyUnit
order by a.name, a.color, a.Price, a.MoneyUnit

Answer 3

select name,color,price,moneyunit,
    sum(case when type='a' then AtAmount  else 0 end ) as AtAmount,
    sum(case when type='a' then AtMoney  else 0 end ) as AtMoney,
    sum(case when type='b' then AtAmount  else 0 end ) as InAmount,
    sum(case when type='b' then AtMoney  else 0 end ) as InMoney,
    sum(case when type='c' then AtAmount  else 0 end ) as OutAmount,
    sum(case when type='c' then AtMoney  else 0 end ) as OutMoney
from (
select *,'a' as type from a
union all
select *,'b' from b
union all
select *,'c' from c
) t
group by name,color,price,moneyunit

Answer 4

这个可以给解释下不？
group by
order by
分别啥作用
left join的好处？

Answer 5

insert into Table4 select name,color,price,moneyunit,
    sum(case when type='a' then AtAmount  else 0 end ) as AtAmount,
    sum(case when type='a' then AtMoney  else 0 end ) as AtMoney,
    sum(case when type='b' then AtAmount  else 0 end ) as InAmount,
    sum(case when type='b' then AtMoney  else 0 end ) as InMoney,
    sum(case when type='c' then AtAmount  else 0 end ) as OutAmount,
    sum(case when type='c' then AtMoney  else 0 end ) as OutMoney
from (
select *,'a' as type from a
union all
select *,'b' from b
union all
select *,'c' from c
) t
group by name,color,price,moneyunit

Answer 6

code=SQL]
create table  table1([name] int ,color varchar(20),price float,atamount int,atmoney int ,moneyunit varchar(20))
insert Table1 
select 001    ,'red' ,   1.2 ,   1200,      1440      ,'RMB'  union all
select 001,    'red',    1.5 ,   1000, 1500,      'RMB' union all
select 002,   ' blue' ,   1.8,    1000,      1800,      'USD' union all
select 003  , ' red',  2.0 ,   500     , 1000      ,'HKD' union all
select 003  ,  'red' ,   1.2  ,  1000 ,     1200   ,   'HKD' union all
select 004   , 'black' , 1.5 ,   900     , 1350   ,'HKD' union all
select 004  ,  'black'  ,1.5 ,   900    ,  1350 ,  'HKD' union all
select 005 ,   'gray'   , 1.2,    1100, 1320,   'RMB' create table  table2([name] int ,color varchar(20),price float,inamount int,inmoney int ,moneyunit varchar(20))
insert table2 
select 001  ,  'red' ,   1.2,    1000      ,1200     ,'RMB' union all
select 001    ,'red' ,  1.2 ,   1000      ,1200    ,  'RMB' union all
select 001    ,'red' ,   1.5 ,   1000 ,1500  ,    'RMB' union all
select 002    ,'blue',  1.8   , 1000  ,    1800,      'USD' union all
select 003    ,'red' ,   2.0   , 1000 ,     2000,      'HKD' union all
select 003    ,'red' ,   1.2    ,1200 ,     1440 ,     'HKD' union all
select 004    ,'black',  1.5    ,900  ,    1350  ,   'HKD' union all
select 005    ,'gray' , 1.2    ,2000 ,2400    , 'RMB' 
create table  table3([name] int ,color varchar(20),price float,outamount int,outmoney int ,moneyunit varchar(20))
insert table3
select 001,    'red' ,   1.2,    400 ,       480 ,       'RMB' union all
select 001 ,   'red' ,   1.2,   400  ,      480  ,      'RMB' union all
select 003  ,  'red' ,   2.0,    500 ,       1000,        'HKD' union all
select 003 ,   'red' ,   1.2,    100 ,       120 ,       'HKD' union all
select 003 ,   'red' ,   1.2,    100 ,       120 ,       'HKD' union all
select 005 ,   'gray',  1.2 ,   900 ,  1080      , 'RMB' 结果得出: 
Table4 
name color  Price MoneyUnit InAmount  InMoney  OutAmount  OutMoney  AtAmount  AtMoney 
001  red    1.2    RMB        2000      2400      800        960      1200      1440 
001  red    1.5    RMB        1000   1500                          1000     1500 
002  blue  1.8    USD        1000      1800                          1000      1800      
003  red    2.0    HKD        1000      2000      500        1000      500        1000 
003  red    1.2    HKD        1200      1440      200        240      1000      1200      
004  black  1.5    HKD        1800      2700                         1800      2700 
005  gray  1.2    RMB        2000   2400      900       1080 1100     1320    select a.name ,a.color, a.price , a.moneyUnit ,
sum(b.inamount) as inamount ,
sum(b.inmoney) as inmoney,
sum(c.outamount) as outamount,
sum(c.outmoney)as outmoney,
sum(a.atamount) as atamount,
sum(a.atmoney)  as atmoney
from  table1  a 
left join table2 b on a.name = b.name and a.color = b.color and a.price  = b.price
left join table3 c on c.name = a.name and c.color = a.color and c.price  = a.price 
group by a.name ,a.price,a.color,a.moneyUnit 
order by a.name, a.color, a.Price, a.MoneyUnit----------------------------------------------------------------
1 red 1.2 RMB 4000 4800 1600 1920 4800 5760
1 red 1.5 RMB 1000 1500 NULL NULL 1000 1500
2  blue 1.8 USD NULL NULL NULL NULL 1000 1800
3  red 2 HKD NULL NULL NULL NULL 500 1000
3 red 1.2 HKD 2400 2880 200 240 2000 2400
4 black 1.5 HKD 1800 2700 NULL NULL 1800 2700
5 gray 1.2 RMB 2000 2400 900 1080 1100 1320
[/code]

Answer 7

create table  table1([name] int ,color varchar(20),price float,atamount int,atmoney int ,moneyunit varchar(20))
insert Table1 
select 001    ,'red' ,   1.2 ,   1200,      1440      ,'RMB'  union all
select 001,    'red',    1.5 ,   1000, 1500,      'RMB' union all
select 002,   ' blue' ,   1.8,    1000,      1800,      'USD' union all
select 003  , ' red',  2.0 ,   500     , 1000      ,'HKD' union all
select 003  ,  'red' ,   1.2  ,  1000 ,     1200   ,   'HKD' union all
select 004   , 'black' , 1.5 ,   900     , 1350   ,'HKD' union all
select 004  ,  'black'  ,1.5 ,   900    ,  1350 ,  'HKD' union all
select 005 ,   'gray'   , 1.2,    1100, 1320,   'RMB' create table  table2([name] int ,color varchar(20),price float,inamount int,inmoney int ,moneyunit varchar(20))
insert table2 
select 001  ,  'red' ,   1.2,    1000      ,1200     ,'RMB' union all
select 001    ,'red' ,  1.2 ,   1000      ,1200    ,  'RMB' union all
select 001    ,'red' ,   1.5 ,   1000 ,1500  ,    'RMB' union all
select 002    ,'blue',  1.8   , 1000  ,    1800,      'USD' union all
select 003    ,'red' ,   2.0   , 1000 ,     2000,      'HKD' union all
select 003    ,'red' ,   1.2    ,1200 ,     1440 ,     'HKD' union all
select 004    ,'black',  1.5    ,900  ,    1350  ,   'HKD' union all
select 005    ,'gray' , 1.2    ,2000 ,2400    , 'RMB' 
create table  table3([name] int ,color varchar(20),price float,outamount int,outmoney int ,moneyunit varchar(20))
insert table3
select 001,    'red' ,   1.2,    400 ,       480 ,       'RMB' union all
select 001 ,   'red' ,   1.2,   400  ,      480  ,      'RMB' union all
select 003  ,  'red' ,   2.0,    500 ,       1000,        'HKD' union all
select 003 ,   'red' ,   1.2,    100 ,       120 ,       'HKD' union all
select 003 ,   'red' ,   1.2,    100 ,       120 ,       'HKD' union all
select 005 ,   'gray',  1.2 ,   900 ,  1080      , 'RMB' 
drop table table1,table2,table3
结果得出: 
Table4 
name color  Price MoneyUnit InAmount  InMoney  OutAmount  OutMoney  AtAmount  AtMoney 
001  red    1.2    RMB        2000      2400      800        960      1200      1440 
001  red    1.5    RMB        1000   1500                          1000     1500 
002  blue  1.8    USD        1000      1800                          1000      1800      
003  red    2.0    HKD        1000      2000      500        1000      500        1000 
003  red    1.2    HKD        1200      1440      200        240      1000      1200      
004  black  1.5    HKD        1800      2700                         1800      2700 
005  gray  1.2    RMB        2000   2400      900       1080 1100     1320    select a.name ,a.color, a.price , a.moneyUnit ,
sum(b.inamount) as inamount ,
sum(b.inmoney) as inmoney,
sum(c.outamount) as outamount,
sum(c.outmoney)as outmoney,
sum(a.atamount) as atamount,
sum(a.atmoney)  as atmoney
from  table1  a 
left join table2 b on a.name = b.name and a.color = b.color and a.price  = b.price
left join table3 c on c.name = a.name and c.color = a.color and c.price  = a.price 
group by a.name ,a.price,a.color,a.moneyUnit 
order by a.name, a.color, a.Price, a.MoneyUnit----------------------------------------------------------------
1 red 1.2 RMB 4000 4800 1600 1920 4800 5760
1 red 1.5 RMB 1000 1500 NULL NULL 1000 1500
2  blue 1.8 USD NULL NULL NULL NULL 1000 1800
3  red 2 HKD NULL NULL NULL NULL 500 1000
3 red 1.2 HKD 2400 2880 200 240 2000 2400
4 black 1.5 HKD 1800 2700 NULL NULL 1800 2700
5 gray 1.2 RMB 2000 2400 900 1080 1100 1320

Answer 8

1~7的还没有试，8、9的都错了，结果都不同。。
1    red    1.2    RMB    4000    4800    1600    1920    4800    5760
1    red    1.5    RMB    1000    1500    NULL    NULL    1000    1500
2     blue    1.8    USD    NULL    NULL    NULL    NULL    1000    1800
3     red    2    HKD    NULL    NULL    NULL    NULL    500    1000
3    red    1.2    HKD    2400    2880    200    240    2000    2400
4    black    1.5    HKD    1800    2700    NULL    NULL    1800    2700
5    gray    1.2    RMB    2000    2400    900    1080    1100    1320001   red    1.2    RMB        2000       2400      800         960      1200       1440 
001   red    1.5    RMB        1000   1500                           1000     1500
002   blue   1.8    USD        1000       1800                           1000       1800       
003   red    2.0    HKD        1000       2000      500        1000      500        1000
003   red    1.2    HKD        1200       1440      200        240       1000       1200       
004   black  1.5    HKD        1800       2700                          1800       2700
005   gray   1.2    RMB        2000   2400      900        1080  1100     1320        

Answer 9

create table  table1([name] int ,color varchar(20),price float,atamount int,atmoney int ,moneyunit varchar(20))
insert Table1 
select 001    ,'red' ,   1.2 ,   1200,      1440      ,'RMB'  union all
select 001,    'red',    1.5 ,   1000, 1500,      'RMB' union all
select 002,   'blue' ,   1.8,    1000,      1800,      'USD' union all
select 003  , 'red',  2.0 ,   500     , 1000      ,'HKD' union all
select 003  ,  'red' ,   1.2  ,  1000 ,     1200   ,   'HKD' union all
select 004   , 'black' , 1.5 ,   900     , 1350   ,'HKD' union all
select 004  ,  'black'  ,1.5 ,   900    ,  1350 ,  'HKD' union all
select 005 ,   'gray'   , 1.2,    1100, 1320,   'RMB' create table  table2([name] int ,color varchar(20),price float,inamount int,inmoney int ,moneyunit varchar(20))
insert table2 
select 001  ,  'red' ,   1.2,    1000      ,1200     ,'RMB' union all
select 001    ,'red' ,  1.2 ,   1000      ,1200    ,  'RMB' union all
select 001    ,'red' ,   1.5 ,   1000 ,1500  ,    'RMB' union all
select 002    ,'blue',  1.8   , 1000  ,    1800,      'USD' union all
select 003    ,'red' ,   2.0   , 1000 ,     2000,      'HKD' union all
select 003    ,'red' ,   1.2    ,1200 ,     1440 ,     'HKD' union all
select 004    ,'black',  1.5    ,900  ,    1350  ,   'HKD' union all
select 005    ,'gray' , 1.2    ,2000 ,2400    , 'RMB' 
create table  table3([name] int ,color varchar(20),price float,outamount int,outmoney int ,moneyunit varchar(20))
insert table3
select 001,    'red' ,   1.2,    400 ,       480 ,       'RMB' union all
select 001 ,   'red' ,   1.2,   400  ,      480  ,      'RMB' union all
select 003  ,  'red' ,   2.0,    500 ,       1000,        'HKD' union all
select 003 ,   'red' ,   1.2,    100 ,       120 ,       'HKD' union all
select 003 ,   'red' ,   1.2,    100 ,       120 ,       'HKD' union all
select 005 ,   'gray',  1.2 ,   900 ,  1080      , 'RMB' 
select [name],color,price,moneyunit,
sum(case when type='b' then AtAmount  else 0 end ) as InAmount,
    sum(case when type='b' then AtMoney  else 0 end ) as InMoney,
    sum(case when type='c' then AtAmount  else 0 end ) as OutAmount,
sum(case when type='c' then AtMoney  else 0 end ) as OutMoney,
    sum(case when type='a' then AtAmount  else 0 end ) as AtAmount,
    sum(case when type='a' then AtMoney  else 0 end ) as AtMoney
    
from (
select *,'a' as type from table1
union all
select *,'b' as type from table2
union all
select *,'c' as type from table3
) t
group by [name],color,price,moneyunit
----------------------------------------------------------------
1 red 1.2 RMB 2000 2400 800 960 1200 1440
1 red 1.5 RMB 1000 1500 0 0 1000 1500
2 blue 1.8 USD 1000 1800 0 0 1000 1800
3 red 1.2 HKD 1200 1440 200 240 1000 1200
3 red 2 HKD 1000 2000 500 1000 500 1000
4 black 1.5 HKD 900 1350 0 0 1800 2700
5 gray 1.2 RMB 2000 2400 900 1080 1100 1320

Answer 10

create table  table1([name] nvarchar(10) ,color varchar(20),price float,atamount int,atmoney int ,moneyunit varchar(20))
insert Table1 select '001','red' ,   1.2 ,   1200,      1440      ,'RMB'  union all
select '001',    'red',    1.5 ,   1000, 1500,      'RMB' union all
select '002',   ' blue' ,   1.8,    1000,      1800,      'USD' union all
select '003'  , ' red',  2.0 ,   500     , 1000      ,'HKD' union all
select '003'  ,  'red' ,   1.2  ,  1000 ,     1200   ,   'HKD' union all
select '004'   , 'black' , 1.5 ,   900     , 1350   ,'HKD' union all
select '004'  ,  'black'  ,1.5 ,   900    ,  1350 ,  'HKD' union all
select '005' ,   'gray'   , 1.2,    1100, 1320,   'RMB' create table  table2([name] nvarchar(10) ,color varchar(20),price float,inamount int,inmoney int ,moneyunit varchar(20))
insert table2 
select '001'  ,  'red' ,   1.2,    1000      ,1200     ,'RMB' union all
select '001'    ,'red' ,  1.2 ,   1000      ,1200    ,  'RMB' union all
select '001'    ,'red' ,   1.5 ,   1000 ,1500  ,    'RMB' union all
select '002'    ,'blue',  1.8   , 1000  ,    1800,      'USD' union all
select '003'    ,'red' ,   2.0   , 1000 ,     2000,      'HKD' union all
select '003'    ,'red' ,   1.2    ,1200 ,     1440 ,     'HKD' union all
select '004'    ,'black',  1.5    ,900  ,    1350  ,   'HKD' union all
select '005'    ,'gray' , 1.2    ,2000 ,2400    , 'RMB' 
create table  table3([name] nvarchar(10) ,color varchar(20),price float,outamount int,outmoney int ,moneyunit varchar(20))
insert table3
select '001',    'red' ,   1.2,    400 ,       480 ,       'RMB' union all
select '001' ,   'red' ,   1.2,   400  ,      480  ,      'RMB' union all
select '003'  ,  'red' ,   2.0,    500 ,       1000,        'HKD' union all
select '003' ,   'red' ,   1.2,    100 ,       120 ,       'HKD' union all
select '003' ,   'red' ,   1.2,    100 ,       120 ,       'HKD' union all
select '005' ,   'gray',  1.2 ,   900 ,  1080      , 'RMB' SELECT [Name] = ISNULL(ISNULL(t1.[Name], t2.[Name]), t3.[Name])
, Color = ISNULL(ISNULL(t1.Color, t2.Color), t3.Color)
, Price = ISNULL(ISNULL(t1.Price, t2.Price), t3.Price)
, MoneyUnit = ISNULL(ISNULL(t1.MoneyUnit, t2.MoneyUnit), t3.MoneyUnit)
, t2.InAmount, t2.InMoney, t3.OutAmount, t3.OutMoney
, t1.AtAmount, t1.AtMoney
FROM
(
SELECT [Name], Color, Price, MoneyUnit
, AtAmount = SUM(AtAmount)
, AtMoney = SUM(AtMoney)
FROM table1
GROUP BY [Name], Color, Price, MoneyUnit
) t1
FULL JOIN
(
SELECT [Name], Color, Price, MoneyUnit
, InAmount = SUM(InAmount)
, InMoney = SUM(InMoney)
FROM table2
GROUP BY [Name], Color, Price, MoneyUnit
) t2
ON t1.[Name] = t2.[Name]
AND t1.Color = t2.Color
AND t1.Price = t2.Price
AND t1.MoneyUnit = t2.MoneyUnit
FULL JOIN
(
SELECT [Name], Color, Price, MoneyUnit
, OutAmount = SUM(OutAmount)
, OutMoney = SUM(OutMoney)
FROM table3
GROUP BY [Name], Color, Price, MoneyUnit
) t3
ON t1.[Name] = t3.[Name]
AND t1.Color = t3.Color
AND t1.Price = t3.Price
AND t1.MoneyUnit = t3.MoneyUnit
ORDER BY [Name], Color, Price, MoneyUnitdrop table table1,table2,table3

Answer 11

Name       Color                Price                  MoneyUnit            InAmount    InMoney     OutAmount   OutMoney    AtAmount    AtMoney
---------- -------------------- ---------------------- -------------------- ----------- ----------- ----------- ----------- ----------- -----------
001        red                  1.2                    RMB                  2000        2400        800         960         1200        1440
001        red                  1.5                    RMB                  1000        1500        NULL        NULL        1000        1500
002         blue                1.8                    USD                  NULL        NULL        NULL        NULL        1000        1800
002        blue                 1.8                    USD                  1000        1800        NULL        NULL        NULL        NULL
003         red                 2                      HKD                  NULL        NULL        NULL        NULL        500         1000
003        red                  1.2                    HKD                  1200        1440        200         240         1000        1200
003        red                  2                      HKD                  NULL        NULL        500         1000        NULL        NULL
003        red                  2                      HKD                  1000        2000        NULL        NULL        NULL        NULL
004        black                1.5                    HKD                  900         1350        NULL        NULL        1800        2700
005        gray                 1.2                    RMB                  2000        2400        900         1080        1100        1320(10 行受影响)