sql查询合并两个表的数据并显示输出
以下是我的两个表。sql查询合并两个表的数据并显示输出
我需要为EX加入这两个表中的数据,并获取相应的结果...
-
在方案主表有8行不同的收据没有。
在收到登记表只有2个收据创建...
,所以我需要只显示那些余额收到方案主表哪一本书和接收不存在在收到登记表。
表的名字 - scheme_master
book_no2 receipt_no createddate
401 10 15-03-2015
401 11 15-03-2015
401 12 15-03-2015
401 13 15-03-2015
403 25 15-03-2015
403 26 15-03-2015
403 27 15-03-2015
403 28 15-03-2015
405 35 15-03-2015
405 36 15-03-2015
405 37 15-03-2015
405 38 15-03-2015
表的名字 - Receipt_entry
book_no receipt_no
401 10
403 26
我需要只有那些收据,不存在领取登记表。
预计输出
Book No Balance Receipt createddate
401 11 15-03-2015
401 12 15-03-2015
401 13 15-03-2015
403 25 15-03-2015
403 27 15-03-2015
403 28 15-03-2015
SQL:
SELECT DISTINCT SM.receipt_no AS Receipt,SM.book_no2 AS book,SM.created AS printedDate,SM.city AS cityname FROM scheme_master SM
LEFT JOIN receipt_entry RE ON RE.receipt_no = SM.receipt_no WHERE
SM.book_no2 BETWEEN :book_no AND :book2 AND RE.receipt_no IS NULL
首先,你应该不需要distinct
,如果你在两个book_no
和receipt_no
正确加入。其次,where
子句中的条件将left join
转换为inner join
,因为NULL
值的比较失败。这种情况应该放在第一张桌子上,而不是第二张桌子上。
所以,试试这个:
SELECT SM.book_no2 AS book, SM.created AS printedDate,
SM.receipt_no AS Receipt, RE.bookingdate AS bookingdate, RE.surveyor_name FROM scheme_master SM LEFT JOIN
receipt_entry RE
ON RE.book_no = SM.book_no2 AND RE.receipt_no = SM.receipt_no
WHERE SM.book_no = 401;
PLZ对不起,其实我现在在我的帖子上发布了错误的问题我更新我的文章和查询也plz帮助实现这一目标.... – 2015-04-01 16:03:27
SELECT SM.book_no2 AS book,SM.created AS printedDate,SM.receipt_no AS Receipt,RE.bookingdate AS bookingdate,RE.surveyor_name
FROM scheme_master SM
INNER JOIN Receipt_entry RE
ON RE.book_no = SM.book_no2 AND RE.receipt_no = SM.receipt_no
WHERE RE.book_no = 401;
,这将是您的原始查询:
SELECT DISTINCT SM.book_no2 AS book,SM.created AS printedDate,SM.receipt_no AS Receipt,RE.bookingdate AS bookingdate,RE.surveyor_name
FROM scheme_master SM
LEFT JOIN receipt_entry RE ON RE.book_no = SM.book_no2
你可以修改它这样一个适合您的需要:
SELECT SM.book_no2 AS book, SM.created AS printedDate,SM.receipt_no AS Receipt, RE.bookingdate AS bookingdate, RE.surveyor_name
FROM scheme_master SM
LEFT JOIN receipt_entry RE
ON RE.book_no = SM.book_no2
AND RE.receipt_no = SM.receipt_no;
我们左边加入了两条语句,查询将在same row
中输出具有相同值book_number
和receipt_number
的值,然后显示带有或不带数据的booking date
和surveyor name
。
编辑:
请检查以下
SELECT x.book,x.printedDate,x.Receipt,x.bookingdate,x.surveyor_name
FROM
(
select SM.book_no2 AS book, SM.created AS printedDate,SM.receipt_no AS Receipt, RE.bookingdate AS bookingdate, RE.surveyor_name,if(RE.book_no is null,0,RE.book_no) book_no,if(RE.receipt_no is null,0,RE.receipt_no) receipt_no
FROM scheme_master SM
LEFT JOIN receipt_entry RE
ON RE.book_no = SM.book_no2
AND RE.receipt_no = SM.receipt_no
union
select if(SM.book_no2 is null,0,SM.book_no2) AS book, SM.created AS printedDate,if(SM.receipt_no is null,0,SM.receipt_no) AS Receipt, RE.bookingdate AS bookingdate, RE.surveyor_name,RE.book_no,RE.receipt_no
FROM scheme_master SM
RIGHT JOIN receipt_entry RE
ON RE.book_no = SM.book_no2
AND RE.receipt_no = SM.receipt_no
) x
WHERE x.book != x.book_no
AND x.Receipt != x.receipt_no
PLZ对不起,其实我发布在我的文章上的错误问题现在我更新我的文章和查询也plz帮助实现这一点.... – 2015-04-01 16:03:34
@payaldubey如果有更新了我的答案,如果它包含任何错误,请通知我。 – Arzgethalm 2015-04-01 16:11:35
您的查询从scheme_master表中获取所有数据,但我只需要那些存在于receipt_entry表中的书不是所有在ex计划主表中的书都有3本书没有401,403,405 ..并且在收据条目表中只有401和403是prset。 ...所以我需要从计划大师只有401和403书的数据,除了在收据输入表中出现的收据... – 2015-04-01 16:35:20
你可以试试下面的查询:
select s.book_no2 as "Book No", s.receipt_no as "Balance Receipt", s.createddate
from scheme s join receipt r on (s.book_no2 = r.book_no)
where s.receipt_no not in (select receipt_no from receipt);
的演示结果可以在这里看到:SQLFiddle
您的查询似乎精细。什么是意想不到的输出? – 2015-04-01 15:54:10
actuall这将获取计划主表中的所有数据..但我只需要获取那些存在于收据条目表中的书的行......对于我的计划主表中的前例,有3本书没有401,403,405 ..并且在收据条目中表只有401和403是prset ....所以我需要从计划大师只有401和403书的数据,除了在收据输入表中出现的那些收据... – 2015-04-01 16:08:12