UNION 2 带有计算列的选择查询

首先我想查询应该连接到计算机的所有打印机.计算机由其 ComputerGUID 标识.(例如:5bec3779-...)我也想知道，哪台打印机将成为默认打印机.一台计算机只能有一台默认打印机.我使用以下查询:SELECT cm.PrinterGUID,案件当 cp.PrinterGUID 为 NULL THEN 0其他 1END AS isDefaultPrinter从计算机测绘厘米剩下加入计算机默认打印机cp开 cm.ComputerGUID = cp.ComputerGUID和 cm.PrinterGUID = cp.PrinterGUID哪里 cm.ComputerGUID = '5bec3779-b002-46ba-97c4-19158c13001f'命令由 cm.PrinterGUID我得到以下结果，没问题:PrinterGUID |是默认打印机---------------------------------------------------------4b4a9288-1368-4ec7-a184-638d432d7d28 |15106f1f7-068f-463f-9b76-7cc0ba017184 |05549f63f-e02f-4685-a976-96b50c299bed |0957b7233-e590-4e7d-aed6-aee0573fc3a8 |0.其次，我对用户也有同样的要求:SELECT usermapping.PrinterGUID,案件当 userdefaultprinter.PrinterGUID 为 NULL THEN 0其他 1END AS isDefaultPrinter从用户映射左加入 userdefaultprinter ON usermapping.UserGUID = userdefaultprinter.UserGUID AND usermapping.PrinterGUID = userdefaultprinter.PrinterGUIDWHERE usermapping.UserGUID = 'd3cf699b-8d71-4dbc-92f3-402950042054'按 usermapping.PrinterGUID 排序结果:PrinterGUID |是默认打印机---------------------------------------------------------4b4a9288-1368-4ec7-a184-638d432d7d28 |1a353199e-cd02-4fa6-904e-c172235abe9f |0.这一切都好.为了简化我的程序，我想用一个 SQL 查询替换这两个查询.所需的结果如下所示:PrinterGUID |isComputerDefaultPrinter |是用户默认打印机---------------------------------------------------------------------------------4b4a9288-1368-4ec7-a184-638d432d7d28 |1 |15106f1f7-068f-463f-9b76-7cc0ba017184 |0 |05549f63f-e02f-4685-a976-96b50c299bed |0 |0957b7233-e590-4e7d-aed6-aee0573fc3a8 |0 |0a353199e-cd02-4fa6-904e-c172235abe9f |0 |0PrinterGUID 在结果中应该是唯一的.当然，用户默认打印机可能与上例中的计算机默认打印机不同.不幸的是，我在 SQL 方面的专业知识有限.根据我的研究，使用 UNION 适合加入两个选择.我开始使用上面的例子作为基础建立一个查询.这是我最终得到的查询，但结果并不理想.SELECT computermapping.PrinterGUID，案件当 computerdefaultprinter.PrinterGUID 为 NULL THEN 0其他 1END AS isComputerDefaultPrinter,null AS isUserDefaultPrinter来自计算机制图在 computermapping.ComputerGUID 上左连接 computerdefaultprinter = computerdefaultprinter.ComputerGUID AND computermapping.PrinterGUID = computerdefaultprinter.PrinterGUIDWHERE computermapping.ComputerGUID = '5bec3779-b002-46ba-97c4-19158c13001f'联盟选择 usermapping.PrinterGUID，null AS isComputerDefaultPrinter，案件当 userdefaultprinter.PrinterGUID 为 NULL THEN 0其他 1END AS isUserDefaultPrinter从用户映射左加入 userdefaultprinter ON usermapping.UserGUID = userdefaultprinter.UserGUID AND usermapping.PrinterGUID = userdefaultprinter.PrinterGUIDWHERE usermapping.UserGUID = 'd3cf699b-8d71-4dbc-92f3-402950042054'你能帮我解决这个问题吗?谢谢！ 解决方案 我认为关键思想是获取您想要的打印机列表:选择 PrinterGUID从 ((选择 cm.PrinterGUID从计算机测绘厘米其中 cm.ComputerGUID = '5bec3779-b002-46ba-97c4-19158c13001f') union -- 删除重复项(选择 PrinterGUID来自用户映射嗯其中 um.UserGUID = 'd3cf699b-8d71-4dbc-92f3-402950042054')) p;然后您可以使用 exists 来查看这些是否为默认值:选择p.PrinterGUID，(存在(选择 1来自计算机默认打印机 cdp其中 cdp.PrinterGUID = p.PrinterGUID 和cdp.ComputerGUID = '5bec3779-b002-46ba-97c4-19158c13001f')) 作为 is_computer_default，(存在(选择 1来自 userdefaultprinter udp其中 udp.PrinterGUID = p.PrinterGUID ANDudp.UserGUID = 'd3cf699b-8d71-4dbc-92f3-402950042054')) as is_user_default从 ((选择 cm.PrinterGUID从计算机测绘厘米其中 cm.ComputerGUID = '5bec3779-b002-46ba-97c4-19158c13001f') union -- 删除重复项(选择 PrinterGUID来自用户映射嗯其中 um.UserGUID = 'd3cf699b-8d71-4dbc-92f3-402950042054')) p;这里是一个数据库小提琴.I have 4 simple tables containing GUIDs in a database.The database schema looks like this:I have created some example data.CREATE TABLE `computermapping` ( `ComputerMappingID` int(11) NOT NULL, `PrinterGUID` char(36) NOT NULL, `ComputerGUID` char(36) NOT NULL);INSERT INTO `computermapping` (`ComputerMappingID`, `PrinterGUID`, `ComputerGUID`) VALUES(43, 'a353199e-cd02-4fa6-904e-c172235abe9f', '87f44dc7-09e9-483c-935c-325b77ea4355'),(44, '5549f63f-e02f-4685-a976-96b50c299bed', '87f44dc7-09e9-483c-935c-325b77ea4355'),(80, 'a353199e-cd02-4fa6-904e-c172235abe9f', '4e392a05-e535-457f-abc3-3f22739aa5ca'),(90, 'd2e9c5b9-43aa-4713-98e5-deb5e09e461b', '6bc91c0e-9aea-4847-895b-d8064e8feae5'),(91, '5da046e3-4fb3-4ca5-89e8-239d81d6dc97', '6bc91c0e-9aea-4847-895b-d8064e8feae5'),(92, '4b4a9288-1368-4ec7-a184-638d432d7d28', '5bec3779-b002-46ba-97c4-19158c13001f'),(93, '5549f63f-e02f-4685-a976-96b50c299bed', '5bec3779-b002-46ba-97c4-19158c13001f'),(94, '957b7233-e590-4e7d-aed6-aee0573fc3a8', '5bec3779-b002-46ba-97c4-19158c13001f'),(95, '5106f1f7-068f-463f-9b76-7cc0ba017184', '5bec3779-b002-46ba-97c4-19158c13001f');CREATE TABLE `computerdefaultprinter` ( `ComputerGUID` char(36) NOT NULL, `PrinterGUID` char(36) NOT NULL);INSERT INTO `computerdefaultprinter` (`ComputerGUID`, `PrinterGUID`) VALUES('5bec3779-b002-46ba-97c4-19158c13001f', '4b4a9288-1368-4ec7-a184-638d432d7d28'),('87f44dc7-09e9-483c-935c-325b77ea4355', 'a353199e-cd02-4fa6-904e-c172235abe9f'),('6bc91c0e-9aea-4847-895b-d8064e8feae5', 'd2e9c5b9-43aa-4713-98e5-deb5e09e461b');CREATE TABLE `usermapping` ( `UserMappingID` int(11) NOT NULL, `PrinterGUID` char(36) NOT NULL, `UserGUID` char(36) NOT NULL);INSERT INTO `usermapping` (`UserMappingID`, `PrinterGUID`, `UserGUID`) VALUES(63, '4b4a9288-1368-4ec7-a184-638d432d7d28', 'd3cf699b-8d71-4dbc-92f3-402950042054'),(64, 'a353199e-cd02-4fa6-904e-c172235abe9f', 'd3cf699b-8d71-4dbc-92f3-402950042054'),(65, 'd2e9c5b9-43aa-4713-98e5-deb5e09e461b', '25c35cf5-ec20-4be5-a7f5-68f6fb93d192'),(66, '5549f63f-e02f-4685-a976-96b50c299bed', '5ca9359d-5492-4b22-bd02-209f104e61d3');CREATE TABLE `userdefaultprinter` ( `UserGUID` char(36) NOT NULL, `PrinterGUID` char(36) NOT NULL);INSERT INTO `userdefaultprinter` (`UserGUID`, `PrinterGUID`) VALUES('d3cf699b-8d71-4dbc-92f3-402950042054', '4b4a9288-1368-4ec7-a184-638d432d7d28'),('5ca9359d-5492-4b22-bd02-209f104e61d3', '5549f63f-e02f-4685-a976-96b50c299bed'),('25c35cf5-ec20-4be5-a7f5-68f6fb93d192', 'd2e9c5b9-43aa-4713-98e5-deb5e09e461b');Link: http://sqlfiddle.com/#!9/a9e4ceAt first I want to query for all Printers that should get connected to a Computer. The Computer is identified by its ComputerGUID. (in example: 5bec3779-...)I also want to know, which Printer will be the Default-Printer. A Computer can only have one Default-Printer.I use the following query:SELECT cm.PrinterGUID,CASE WHEN cp.PrinterGUID IS NULL THEN 0 ELSE 1END AS isDefaultPrinter FROM computermapping cm LEFT JOIN computerdefaultprinter cp ON cm.ComputerGUID = cp.ComputerGUID AND cm.PrinterGUID = cp.PrinterGUID WHERE cm.ComputerGUID = '5bec3779-b002-46ba-97c4-19158c13001f' ORDER BY cm.PrinterGUIDI get the follwoing result, which is OK:PrinterGUID | isDefaultPrinter---------------------------------------------------------4b4a9288-1368-4ec7-a184-638d432d7d28 | 15106f1f7-068f-463f-9b76-7cc0ba017184 | 05549f63f-e02f-4685-a976-96b50c299bed | 0957b7233-e590-4e7d-aed6-aee0573fc3a8 | 0.Second I have the same for users:SELECT usermapping.PrinterGUID,CASE WHEN userdefaultprinter.PrinterGUID IS NULL THEN 0 ELSE 1END AS isDefaultPrinterFROM usermappingLEFT JOIN userdefaultprinter ON usermapping.UserGUID = userdefaultprinter.UserGUID AND usermapping.PrinterGUID = userdefaultprinter.PrinterGUIDWHERE usermapping.UserGUID = 'd3cf699b-8d71-4dbc-92f3-402950042054'ORDER BY usermapping.PrinterGUIDResult:PrinterGUID | isDefaultPrinter---------------------------------------------------------4b4a9288-1368-4ec7-a184-638d432d7d28 | 1a353199e-cd02-4fa6-904e-c172235abe9f | 0.This is all OK. To simplify my program, I want to replace those 2 query's by a single SQL-query.The desired result would look like this:PrinterGUID | isComputerDefaultPrinter | isUserDefaultPrinter----------------------------------------------------------------------------------------4b4a9288-1368-4ec7-a184-638d432d7d28 | 1 | 15106f1f7-068f-463f-9b76-7cc0ba017184 | 0 | 05549f63f-e02f-4685-a976-96b50c299bed | 0 | 0957b7233-e590-4e7d-aed6-aee0573fc3a8 | 0 | 0a353199e-cd02-4fa6-904e-c172235abe9f | 0 | 0The PrinterGUID should be Unique in the result. Of course the User Default-Printer may not be the same as the Computer Default-Printer like in the example above.EDIT:Unfortunately my know-how in SQL is limited. As of my research using a UNION would be suitable to join two selects. I started to build up a query using the above example as base. This is the query I ended up with, but the result is not as desired.SELECT computermapping.PrinterGUID,CASE WHEN computerdefaultprinter.PrinterGUID IS NULL THEN 0 ELSE 1END AS isComputerDefaultPrinter,null AS isUserDefaultPrinterFROM computermappingLEFT JOIN computerdefaultprinter ON computermapping.ComputerGUID = computerdefaultprinter.ComputerGUID AND computermapping.PrinterGUID = computerdefaultprinter.PrinterGUIDWHERE computermapping.ComputerGUID = '5bec3779-b002-46ba-97c4-19158c13001f'UNIONSELECT usermapping.PrinterGUID,null AS isComputerDefaultPrinter,CASE WHEN userdefaultprinter.PrinterGUID IS NULL THEN 0 ELSE 1END AS isUserDefaultPrinterFROM usermappingLEFT JOIN userdefaultprinter ON usermapping.UserGUID = userdefaultprinter.UserGUID AND usermapping.PrinterGUID = userdefaultprinter.PrinterGUIDWHERE usermapping.UserGUID = 'd3cf699b-8d71-4dbc-92f3-402950042054'Could you please help me to solve this?Thank you! 解决方案 I think the key idea is to get the list of printers that you want:select PrinterGUIDfrom ((select cm.PrinterGUID from computermapping cm where cm.ComputerGUID = '5bec3779-b002-46ba-97c4-19158c13001f' ) union -- to remove duplicates (select PrinterGUID from usermapping um where um.UserGUID = 'd3cf699b-8d71-4dbc-92f3-402950042054' ) ) p;Then you can use exists to see if these are the defaults:select p.PrinterGUID, (exists (select 1 from computerdefaultprinter cdp where cdp.PrinterGUID = p.PrinterGUID and cdp.ComputerGUID = '5bec3779-b002-46ba-97c4-19158c13001f' ) ) as is_computer_default, (exists (select 1 from userdefaultprinter udp where udp.PrinterGUID = p.PrinterGUID AND udp.UserGUID = 'd3cf699b-8d71-4dbc-92f3-402950042054' ) ) as is_user_defaultfrom ((select cm.PrinterGUID from computermapping cm where cm.ComputerGUID = '5bec3779-b002-46ba-97c4-19158c13001f' ) union -- to remove duplicates (select PrinterGUID from usermapping um where um.UserGUID = 'd3cf699b-8d71-4dbc-92f3-402950042054' ) ) p;Here is a db<>fiddle. 这篇关于UNION 2 带有计算列的选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 1403页，肝出来的..