SQL multiple Table federated query

Source: Internet
Author: User
Keywords Network programming Database synthesis
Tags code create date joint multiple name network network programming

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.