首页 / MYSQL / 在MySQL中为多个属性添加列
在MySQL中为多个属性添加列
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了在MySQL中为多个属性添加列,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4610字,纯文字阅读大概需要7分钟。
内容图文
![在MySQL中为多个属性添加列](/upload/InfoBanner/zyjiaocheng/883/6a665e4b3d8e479cbdab4bf1401173d8.jpg)
我真的不确定如何解释这一点,因为今天我才开始尝试学习如何以更高级的方式使用MySQL查询.这是我所拥有的:
SELECT pa.displayvalue as Brand
FROM product p
JOIN categoryheader ch ON ch.templatetype = 0 AND p.categoryid=ch.categoryid AND ch.isactive=1
JOIN categorydisplayattributes cda ON ch.headerid = cda.headerid AND ch.categoryid = cda.categoryid AND cda.templatetype = ch.templatetype AND cda.isactive=1
JOIN productattribute pa ON p.productid = pa.productid AND cda.attributeid = pa.attributeid AND pa.localeid = 1 AND pa.isactive =1
JOIN attributenames an ON pa.attributeid = an.attributeid AND an.localeid = 1
WHERE p.isactive = 1 AND p.categoryid = 4871
AND an.attributeid = 113319;
因此,我有了该查询,从字面上看,我要做的就是为“ And an.attributeid = 113319”添加另一列
我只想添加另一列,而不是113319,我希望它将例如1762的值拉到其旁边的列中.
因此,第1列将具有“和an.attributeid = 113319”提取的所有值,而第2列将具有“和an.attributeid = 1762”提取的所有值.
我得到的这段代码可以工作:
SELECT pa.displayvalue as Brand
FROM product p
JOIN categoryheader ch ON ch.templatetype = 0 AND p.categoryid=ch.categoryid AND ch.isactive=1
JOIN categorydisplayattributes cda ON ch.headerid = cda.headerid AND ch.categoryid = cda.categoryid AND cda.templatetype = ch.templatetype AND cda.isactive=1
JOIN productattribute pa ON p.productid = pa.productid AND cda.attributeid = pa.attributeid AND pa.localeid = 1 AND pa.isactive =1
JOIN attributenames an ON pa.attributeid = an.attributeid AND an.localeid = 1
WHERE p.isactive = 1 AND p.categoryid = 4871
AND an.attributeid = 113319;
SELECT pa.displayvalue as Type
FROM product p
JOIN categoryheader ch ON ch.templatetype = 0 AND p.categoryid=ch.categoryid AND ch.isactive=1
JOIN categorydisplayattributes cda ON ch.headerid = cda.headerid AND ch.categoryid = cda.categoryid AND cda.templatetype = ch.templatetype AND cda.isactive=1
JOIN productattribute pa ON p.productid = pa.productid AND cda.attributeid = pa.attributeid AND pa.localeid = 1 AND pa.isactive =1
JOIN attributenames an ON pa.attributeid = an.attributeid AND an.localeid = 1
WHERE p.isactive = 1 AND p.categoryid = 4871
AND an.attributeid = 1762;
但这在navicat中作为两个单独的结果返回.我希望这两个结果在一个结果中但作为两列,而不是两个结果各为一列.
如您所见,除了最后一行,所有这些Select中的所有行都是相同的.
我还觉得,有一种更好的方法,那就是不必为获得an.attributeid的另一列而两次使用整个代码.无论如何,我会尽力而为.
如果您有任何建议,我将非常感谢.
解决方法:
如果您确实要这样做,则出于格式化或报告目的.这是您将要使用的一种技巧.
使用LEFT JOIN将返回左表中的所有行,并在右表中返回匹配的行.如果没有匹配项,则结果在右侧为NULL.
SELECT p.productid, t2.Type, t1.Brand, t3.Resolution
FROM product p
JOIN
(
SELECT p.productid, pa.displayvalue as Brand
FROM product p
JOIN categoryheader ch ON ch.templatetype = 0 AND p.categoryid=ch.categoryid AND ch.isactive=1
JOIN categorydisplayattributes cda ON ch.headerid = cda.headerid AND ch.categoryid = cda.categoryid AND cda.templatetype = ch.templatetype AND cda.isactive=1
JOIN productattribute pa ON p.productid = pa.productid AND cda.attributeid = pa.attributeid AND pa.localeid = 1 AND pa.isactive =1
JOIN attributenames an ON pa.attributeid = an.attributeid AND an.localeid = 1
WHERE p.isactive = 1 AND p.categoryid = 4871
AND an.attributeid = 113319
) t1 ON t1.productid = p.productid
LEFT OUTER JOIN
(
SELECT p.productid, pa.displayvalue as Type
FROM product p
JOIN categoryheader ch ON ch.templatetype = 0 AND p.categoryid=ch.categoryid AND ch.isactive=1
JOIN categorydisplayattributes cda ON ch.headerid = cda.headerid AND ch.categoryid = cda.categoryid AND cda.templatetype = ch.templatetype AND cda.isactive=1
JOIN productattribute pa ON p.productid = pa.productid AND cda.attributeid = pa.attributeid AND pa.localeid = 1 AND pa.isactive =1
JOIN attributenames an ON pa.attributeid = an.attributeid AND an.localeid = 1
WHERE p.isactive = 1 AND p.categoryid = 4871
AND an.attributeid = 1100
) t2 ON t2.productid = p.productid
LEFT OUTER JOIN
(
SELECT p.productid, pa.displayvalue as Resolution
FROM product p
JOIN categoryheader ch ON ch.templatetype = 0 AND p.categoryid=ch.categoryid AND ch.isactive=1
JOIN categorydisplayattributes cda ON ch.headerid = cda.headerid AND ch.categoryid = cda.categoryid AND cda.templatetype = ch.templatetype AND cda.isactive=1
JOIN productattribute pa ON p.productid = pa.productid AND cda.attributeid = pa.attributeid AND pa.localeid = 1 AND pa.isactive =1
JOIN attributenames an ON pa.attributeid = an.attributeid AND an.localeid = 1
WHERE p.isactive = 1 AND p.categoryid = 4871
AND an.attributeid = 1762
) t3 ON t3.productid = p.productid
内容总结
以上是互联网集市为您收集整理的在MySQL中为多个属性添加列全部内容,希望文章能够帮你解决在MySQL中为多个属性添加列所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。