SQL multi-table joint Query

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