There are many methods for SQL multi-table joint query. Today we will talk about the usage and instance code of SQL multi-Table query. select & nbsp; * from (select) is generally used) union. Select Type, sum (total amount) from (select item name, quantity, date, type, total amount from Warehouse picking table where Date & gt; 2010-5-500: 00: 00 unio has many methods for SQL multi-table joint query. Today we will talk about the usage of SQL multi-Table query and the instance code. select * from (select) union is generally used for the operation.
Select Type, sum (total amount) from
(Select item name, quantity, date, type, total amount from Warehouse picking table
Where date> '2017-5-5 00:00:00'
Union
Select item name, quantity, date, type, total amount from warehouse receiving table
Where date> '2017-5-5 00:00:00'
Union
Select item name, quantity, date, type, total amount from sales table
Where date> '2017-5-5 00:00:00 ')
Group by type
Next let's take a look at the instance
Three tables
Billing table
Id flowid totall
1 k1 3000
Detail table
Id flowid pid num
1 k1 1 2000
2 k1 2 1000
Payment method table
Id pname
1. Cash
2 Check
3 card swiping
Create table pay (id int, flowid varchar (10), totall int)
Insert into pay select 1, 'k1 ', 3000
Create table detail (id int, flowid varchar (10), pid int, num int)
Insert into detail select 1, 'k1 ',
Insert into detail select 2, 'k1 ',
Create table mode (id int, pname varchar (10 ))
Insert into mode select 1, 'cash'
Insert into mode select 2, 'check'
Insert into mode select 3, 'swipe card'
Declare @ SQL varchar (8000)
Set @ SQL =''
Select @ SQL = @ SQL + ', [' + pname + '] = sum (case B. pid when' + rtrim (id) + 'then num else 0 end )'
From mode
Set @ SQL = 'select. id,. flowid,. totall' + @ SQL + 'from pay a, detail B where. flowid = B. flowid group by. id,. flowid,. totall'
Exec (@ SQL)
Result:
Id flowid totall cash check card swipe
1 k1 3000 2000 1000 0