About SQL multiple table joint Query method A lot of, we are today to talk about SQL multiple table query usage and instance code, generally use the SELECT * FROM (SELECT) union to operate.
Select Type, SUM (total amount) from
(select commodity name, quantity, date, type, total amount from out of library table
where date > ' 2010-5-5 00:00:00 '
Union
Select commodity name, quantity, date, type, total amount from warehousing table
where date > ' 2010-5-5 00:00:00 '
Union
Select commodity name, quantity, date, type, total amount from sales table
where date > ' 2010-5-5 00:00:00 ')
GROUP By Type
Let's take a look at the example
Three tables
Charge table
ID Flowid totall
1 K1 3000
Detailed table
ID flowid pid num
1 K1 1 2000
2 K1 2 1000
Payment Form
ID pname
1 cash
2 Checks
3 Card
CREATE TABLE Pay (ID int,flowid varchar (), Totall int)
INSERT into pay Select 1, ' K1 ', 3000
CREATE TABLE detail (ID int,flowid varchar (), PID int,num int)
INSERT into detail Select 1, ' K1 ', 1,2000
INSERT INTO detail select 2, ' K1 ', 2,1000
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 '
DECLARE @sql varchar (8000)
Set @sql = '
Select @sql = @sql + ', [' +pname+ ']=sum (case b.pid when ' +rtrim (ID) + ' then num else 0 ') '
From mode
Set @sql = ' Select A.id,a.flowid,a.totall ' + @sql + ' pay A,detail b where A.flowid=b.flowid GROUP by A.id,a.flowid, A.totall '
EXEC (@sql)
Results:
ID flowid totall Cash Cheque Card
1 K1 3000 2000 1000 0