我真的不确定如何解释这个,因为我今天刚开始尝试学习如何更高级地使用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的值提取到旁边的列中。
所以第一列会有“and an.attributeid = 113319”拉取的所有值,第二列会有“and 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中的行都是相同的,除了最后一行。
我还觉得有一种更好的方法来获取另一个属性ID列,而不必两次使用整个代码。无论如何,我会尽力而为。
如果您有任何建议,我将非常感激。