UNION 2 带有计算列的选择查询 [英] UNION 2 Select-queries with computed columns

查看：43
本文介绍了UNION 2 带有计算列的选择查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在数据库中有 4 个包含 GUID 的简单表.
数据库架构如下所示:

我已经创建了一些示例数据.

CREATE TABLE `computermapping` (`ComputerMappingID` int(11) 非空，`PrinterGUID` char(36) 非空，`ComputerGUID` 字符(36)非空);INSERT INTO `computermapping`(`ComputerMappingID`、`PrinterGUID`、`ComputerGUID`)值(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');创建表`computerdefaultprinter`(`ComputerGUID` char(36) 非空，`PrinterGUID` 字符(36)非空);插入`computerdefaultprinter`(`ComputerGUID`，`PrinterGUID`)值('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');创建表`用户映射`(`UserMappingID` int(11) 非空，`PrinterGUID` char(36) 非空，`UserGUID` 字符(36)非空);插入`usermapping`(`UserMappingID`、`PrinterGUID`、`UserGUID`)值(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');创建表`userdefaultprinter`(`UserGUID` 字符(36)非空，`PrinterGUID` 字符(36)非空);插入`userdefaultprinter`(`UserGUID`，`PrinterGUID`)值('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');

链接:http://sqlfiddle.com/#!9/a9e4ce

首先我想查询应该连接到计算机的所有打印机.计算机由其 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 |0

PrinterGUID 在结果中应该是唯一的.当然，用户默认打印机可能与上例中的计算机默认打印机不同.

不幸的是，我在 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/a9e4ce

At 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 1 
END 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.PrinterGUID

I get the follwoing result, which is OK:

PrinterGUID                          | isDefaultPrinter
---------------------------------------------------------
4b4a9288-1368-4ec7-a184-638d432d7d28 | 1
5106f1f7-068f-463f-9b76-7cc0ba017184 | 0
5549f63f-e02f-4685-a976-96b50c299bed | 0
957b7233-e590-4e7d-aed6-aee0573fc3a8 | 0

.

Second I have the same for users:

SELECT usermapping.PrinterGUID,
CASE 
 WHEN userdefaultprinter.PrinterGUID IS NULL THEN 0 
 ELSE 1 
END AS isDefaultPrinter
FROM usermapping
LEFT JOIN userdefaultprinter ON usermapping.UserGUID = userdefaultprinter.UserGUID AND usermapping.PrinterGUID = userdefaultprinter.PrinterGUID 
WHERE usermapping.UserGUID = 'd3cf699b-8d71-4dbc-92f3-402950042054'
ORDER BY usermapping.PrinterGUID

Result:

PrinterGUID                          | isDefaultPrinter
---------------------------------------------------------
4b4a9288-1368-4ec7-a184-638d432d7d28 | 1
a353199e-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                        | 1
5106f1f7-068f-463f-9b76-7cc0ba017184 | 0                        | 0
5549f63f-e02f-4685-a976-96b50c299bed | 0                        | 0
957b7233-e590-4e7d-aed6-aee0573fc3a8 | 0                        | 0
a353199e-cd02-4fa6-904e-c172235abe9f | 0                        | 0

The 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 1 
END AS isComputerDefaultPrinter,
null AS isUserDefaultPrinter
FROM computermapping 
LEFT JOIN computerdefaultprinter ON computermapping.ComputerGUID = computerdefaultprinter.ComputerGUID AND computermapping.PrinterGUID = computerdefaultprinter.PrinterGUID 
WHERE computermapping.ComputerGUID = '5bec3779-b002-46ba-97c4-19158c13001f'

UNION

SELECT usermapping.PrinterGUID,
null AS isComputerDefaultPrinter,
CASE 
 WHEN userdefaultprinter.PrinterGUID IS NULL THEN 0 
 ELSE 1 
END AS isUserDefaultPrinter
FROM usermapping
LEFT JOIN userdefaultprinter ON usermapping.UserGUID = userdefaultprinter.UserGUID AND usermapping.PrinterGUID = userdefaultprinter.PrinterGUID 
WHERE 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 PrinterGUID
from ((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_default
from ((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 带有计算列的选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆