sql语句中不需要的附加记录
sql语句中不需要的附加记录
提问于 2016-03-19 15:41:52
- mysql: ver14.14远端5.5.45，用于Win64 (x86)
- O/S: Windows 2008 R2
“促销”表
SELECT id, name FROM promotion ORDER BY id;给出以下结果：
+----+--------------------------+
| id | name |
+----+--------------------------+
| 1 | Shared Web Hosting |
| 4 | cPanel VPS |
| 5 | Unmanaged VPS |
| 6 | Windows VPS |
| 7 | Dedicated Linux |
| 8 | Dedicated Windows Server |
| 9 | Maintained Webhosting |
| 10 | Domain Names |
| 11 | Web Design |
+----+--------------------------+“包装”表
SELECT a.id AS PackageID, a.planname AS PackageName, a.planid AS CategoryID
FROM package a
WHERE a.planid = 11
ORDER BY a.planid, a.planname;给出以下结果：
+-----------+--------------+------------+
| PackageID | PackageName | CategoryID |
+-----------+--------------+------------+
| 40 | Innovative-1 | 11 |
| 46 | Innovative-2 | 11 |
| 47 | Innovative-3 | 11 |
| 38 | Intrinsic-1 | 11 |
| 42 | Intrinsic-2 | 11 |
| 43 | Intrinsic-3 | 11 |
| 39 | Median-1 | 11 |
| 44 | Median-2 | 11 |
| 45 | Median-3 | 11 |
| 41 | Quantum | 11 |
+-----------+--------------+------------+加入“推广”及“套餐”桌
SELECT a.id AS PackageID, a.planname AS PackageName, a.planid
AS CategoryID, b.name
AS CategoryName
FROM package a
LEFT OUTER JOIN promotion b
ON a.planid = b.id
ORDER BY a.planid, a.planname;给出了以下结果：
+-----------+----------------+------------+--------------------------+
| PackageID | PackageName | CategoryID | CategoryName |
+-----------+----------------+------------+--------------------------+
| 1 | Web Hosting | 1 | Shared Web Hosting |
| 2 | VPS-1 | 4 | cPanel VPS |
| 3 | VPS-2 | 4 | cPanel VPS |
| 4 | VPS-3 | 4 | cPanel VPS |
| 5 | VPS-4 | 4 | cPanel VPS |
| 6 | VPS-5 | 4 | cPanel VPS |
| 7 | VPS-6 | 4 | cPanel VPS |
| 8 | uVPS-1 | 5 | Unmanaged VPS |
| 9 | uVPS-2 | 5 | Unmanaged VPS |
| 10 | uVPS-3 | 5 | Unmanaged VPS |
| 11 | uVPS-4 | 5 | Unmanaged VPS |
| 12 | uVPS-5 | 5 | Unmanaged VPS |
| 13 | uVPS-6 | 5 | Unmanaged VPS |
| 14 | wVPS-1 | 6 | Windows VPS |
| 15 | wVPS-2 | 6 | Windows VPS |
| 16 | wVPS-3 | 6 | Windows VPS |
| 17 | wVPS-4 | 6 | Windows VPS |
| 18 | wVPS-5 | 6 | Windows VPS |
| 19 | wVPS-6 | 6 | Windows VPS |
| 20 | Server 1 | 7 | Dedicated Linux |
| 21 | Server 2 | 7 | Dedicated Linux |
| 22 | Server 3 | 7 | Dedicated Linux |
| 23 | Server 4 | 7 | Dedicated Linux |
| 24 | wServer 1 | 8 | Dedicated Windows Server |
| 25 | wServer 2 | 8 | Dedicated Windows Server |
| 26 | wServer 3 | 8 | Dedicated Windows Server |
| 27 | wServer 4 | 8 | Dedicated Windows Server |
| 35 | Advanced-1 | 9 | Maintained Webhosting |
| 36 | Advanced-2 | 9 | Maintained Webhosting |
| 37 | Advanced-3 | 9 | Maintained Webhosting |
| 29 | Basic-1 | 9 | Maintained Webhosting |
| 30 | Basic-2 | 9 | Maintained Webhosting |
| 31 | Basic-3 | 9 | Maintained Webhosting |
| 32 | Intermediate-1 | 9 | Maintained Webhosting |
| 33 | Intermediate-2 | 9 | Maintained Webhosting |
| 34 | Intermediate-3 | 9 | Maintained Webhosting |
| 55 | BIZ | 10 | Domain Names |
| 58 | BZ | 10 | Domain Names |
| 51 | CC | 10 | Domain Names |
| 28 | COM | 10 | Domain Names |
| 53 | INFO | 10 | Domain Names |
| 48 | NET | 10 | Domain Names |
| 56 | NU | 10 | Domain Names |
| 49 | ORG | 10 | Domain Names |
| 52 | TV | 10 | Domain Names |
| 50 | US | 10 | Domain Names |
| 57 | WS | 10 | Domain Names |
| 40 | Innovative-1 | 11 | Web Design |
| 46 | Innovative-2 | 11 | Web Design |
| 47 | Innovative-3 | 11 | Web Design |
| 38 | Intrinsic-1 | 11 | Web Design |
| 42 | Intrinsic-2 | 11 | Web Design |
| 43 | Intrinsic-3 | 11 | Web Design |
| 39 | Median-1 | 11 | Web Design |
| 44 | Median-2 | 11 | Web Design |
| 45 | Median-3 | 11 | Web Design |
| 41 | Quantum | 11 | Web Design |
+-----------+----------------+------------+--------------------------+现在，当我执行这个SQL命令时：
SELECT a.id
AS PackageID, a.planname
AS PackageName, a.planid
AS CategoryID, b.name
AS CategoryName
FROM package a
LEFT OUTER JOIN promotion b
ON a.planid = b.id AND b.id = 11
ORDER BY a.planid, a.planname;给出了以下结果：
+-----------+----------------+------------+--------------+
| PackageID | PackageName | CategoryID | CategoryName |
+-----------+----------------+------------+--------------+
| 1 | Web Hosting | 1 | NULL |
| 2 | VPS-1 | 4 | NULL |
| 3 | VPS-2 | 4 | NULL |
| 4 | VPS-3 | 4 | NULL |
| 5 | VPS-4 | 4 | NULL |
| 6 | VPS-5 | 4 | NULL |
| 7 | VPS-6 | 4 | NULL |
| 8 | uVPS-1 | 5 | NULL |
| 9 | uVPS-2 | 5 | NULL |
| 10 | uVPS-3 | 5 | NULL |
| 11 | uVPS-4 | 5 | NULL |
| 12 | uVPS-5 | 5 | NULL |
| 13 | uVPS-6 | 5 | NULL |
| 14 | wVPS-1 | 6 | NULL |
| 15 | wVPS-2 | 6 | NULL |
| 16 | wVPS-3 | 6 | NULL |
| 17 | wVPS-4 | 6 | NULL |
| 18 | wVPS-5 | 6 | NULL |
| 19 | wVPS-6 | 6 | NULL |
| 20 | Server 1 | 7 | NULL |
| 21 | Server 2 | 7 | NULL |
| 22 | Server 3 | 7 | NULL |
| 23 | Server 4 | 7 | NULL |
| 24 | wServer 1 | 8 | NULL |
| 25 | wServer 2 | 8 | NULL |
| 26 | wServer 3 | 8 | NULL |
| 27 | wServer 4 | 8 | NULL |
| 35 | Advanced-1 | 9 | NULL |
| 36 | Advanced-2 | 9 | NULL |
| 37 | Advanced-3 | 9 | NULL |
| 29 | Basic-1 | 9 | NULL |
| 30 | Basic-2 | 9 | NULL |
| 31 | Basic-3 | 9 | NULL |
| 32 | Intermediate-1 | 9 | NULL |
| 33 | Intermediate-2 | 9 | NULL |
| 34 | Intermediate-3 | 9 | NULL |
| 55 | BIZ | 10 | NULL |
| 58 | BZ | 10 | NULL |
| 51 | CC | 10 | NULL |
| 28 | COM | 10 | NULL |
| 53 | INFO | 10 | NULL |
| 48 | NET | 10 | NULL |
| 56 | NU | 10 | NULL |
| 49 | ORG | 10 | NULL |
| 52 | TV | 10 | NULL |
| 50 | US | 10 | NULL |
| 57 | WS | 10 | NULL |
| 40 | Innovative-1 | 11 | Web Design |
| 46 | Innovative-2 | 11 | Web Design |
| 47 | Innovative-3 | 11 | Web Design |
| 38 | Intrinsic-1 | 11 | Web Design |
| 42 | Intrinsic-2 | 11 | Web Design |
| 43 | Intrinsic-3 | 11 | Web Design |
| 39 | Median-1 | 11 | Web Design |
| 44 | Median-2 | 11 | Web Design |
| 45 | Median-3 | 11 | Web Design |
| 41 | Quantum | 11 | Web Design |
+-----------+----------------+------------+--------------+现在，当我导出以下SQL语句时，给出了上述信息：
SELECT a.id
AS PackageID, a.planname
AS PackageName, a.planid
AS CategoryID, b.name
AS CategoryName
FROM package a
LEFT OUTER JOIN promotion b
ON a.planid = b.id AND b.name = 'Web Design'
ORDER BY a.planid, a.planname;我得到以下结果：
+-----------+----------------+------------+--------------+
| PackageID | PackageName | CategoryID | CategoryName |
+-----------+----------------+------------+--------------+
| 1 | Web Hosting | 1 | NULL |
| 2 | VPS-1 | 4 | NULL |
| 3 | VPS-2 | 4 | NULL |
| 4 | VPS-3 | 4 | NULL |
| 5 | VPS-4 | 4 | NULL |
| 6 | VPS-5 | 4 | NULL |
| 7 | VPS-6 | 4 | NULL |
| 8 | uVPS-1 | 5 | NULL |
| 9 | uVPS-2 | 5 | NULL |
| 10 | uVPS-3 | 5 | NULL |
| 11 | uVPS-4 | 5 | NULL |
| 12 | uVPS-5 | 5 | NULL |
| 13 | uVPS-6 | 5 | NULL |
| 14 | wVPS-1 | 6 | NULL |
| 15 | wVPS-2 | 6 | NULL |
| 16 | wVPS-3 | 6 | NULL |
| 17 | wVPS-4 | 6 | NULL |
| 18 | wVPS-5 | 6 | NULL |
| 19 | wVPS-6 | 6 | NULL |
| 20 | Server 1 | 7 | NULL |
| 21 | Server 2 | 7 | NULL |
| 22 | Server 3 | 7 | NULL |
| 23 | Server 4 | 7 | NULL |
| 24 | wServer 1 | 8 | NULL |
| 25 | wServer 2 | 8 | NULL |
| 26 | wServer 3 | 8 | NULL |
| 27 | wServer 4 | 8 | NULL |
| 35 | Advanced-1 | 9 | NULL |
| 36 | Advanced-2 | 9 | NULL |
| 37 | Advanced-3 | 9 | NULL |
| 29 | Basic-1 | 9 | NULL |
| 30 | Basic-2 | 9 | NULL |
| 31 | Basic-3 | 9 | NULL |
| 32 | Intermediate-1 | 9 | NULL |
| 33 | Intermediate-2 | 9 | NULL |
| 34 | Intermediate-3 | 9 | NULL |
| 55 | BIZ | 10 | NULL |
| 58 | BZ | 10 | NULL |
| 51 | CC | 10 | NULL |
| 28 | COM | 10 | NULL |
| 53 | INFO | 10 | NULL |
| 48 | NET | 10 | NULL |
| 56 | NU | 10 | NULL |
| 49 | ORG | 10 | NULL |
| 52 | TV | 10 | NULL |
| 50 | US | 10 | NULL |
| 57 | WS | 10 | NULL |
| 40 | Innovative-1 | 11 | Web Design |
| 46 | Innovative-2 | 11 | Web Design |
| 47 | Innovative-3 | 11 | Web Design |
| 38 | Intrinsic-1 | 11 | Web Design |
| 42 | Intrinsic-2 | 11 | Web Design |
| 43 | Intrinsic-3 | 11 | Web Design |
| 39 | Median-1 | 11 | Web Design |
| 44 | Median-2 | 11 | Web Design |
| 45 | Median-3 | 11 | Web Design |
| 41 | Quantum | 11 | Web Design |
+-----------+----------------+------------+--------------+我需要的只是"Web设计“记录，我需要在SQL语句中更改什么才能实现这一点？
回答 1
Stack Overflow用户
回答已采纳
发布于 2016-03-19 15:46:06
我习惯了MSSQL和简单的联接，忘记了外部连接选择了所有记录。所以我把左边的外部连接改为内部连接，它工作得很好。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/36103692
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号