首页 文章

从具有不同值的单个表中多次选择一列

提问于
浏览 次
1

嗨，我有一个问题，因为我被困在我的一个SQL查询 . 我写了这个sql查询：

SELECT DISTINCT Date(createddate) as Date,
       (SELECT maxage FROM wob_stock_age 
        WHERE (maxage = a.maxage) AND (totetype='B') 
        GROUP BY Date)AS Blue,

       (SELECT maxage FROM wob_stock_age 
        WHERE (maxage = a.maxage) AND (totetype='V') 
        GROUP BY Date)AS Yellow,

       (SELECT maxage FROM wob_stock_age 
        WHERE (maxage = a.maxage) AND (totetype='N') 
        GROUP BY Date)AS Pink
FROM wob_stock_age as a

如您所见，我在不同的别名下多次选择同一列 . 然而，结果并不是我所期望的，因为我得到Null值 .

enter image description here

UPDATED: 实际上我想为每个日期获得一行，并希望在一行中获得所有"totetype" PINK，BLUE和YELLOW . 而不是一个日期的3行 .

结果就是

|Date       |Blue|Yellow|Pink|
    ------------------------------
    |2016-02-16 |153 |27    |40  |
    ------------------------------
    |2016-02-17 |152 |26    |40  |
mysql sql

2 回答

  • 2

    您可以使用条件聚合执行此操作 . 你的问题不清楚，但我对你要做的最好的猜测是：

    SELECT maxage,
       SUM(CASE WHEN totetype = 'B' THEN 1 ELSE 0 END) as Blue,
       SUM(CASE WHEN totetype = 'V' THEN 1 ELSE 0 END) as Yellow,
       SUM(CASE WHEN totetype = 'N' THEN 1 ELSE 0 END) as Pink
FROM wob_stock_age as a
GROUP BY maxage
ORDER BY maxage ;
    回复于
  • 2

    使用 OUTER APPLY

    SELECT DISTINCT Date(createddate) as [Date],
  BlueTable.Blue,
  YellowTable.Yellow,
  PinkTable.Pink 
 FROM wob_stock_age as a
 OUTER APPLY (
   SELECT maxage as Blue
   FROM wob_stock_age 
   WHERE (maxage = a.maxage) AND (totetype='B') 
   --GROUP BY Date
 )BlueTable
 OUTER APPLY
 (
  SELECT maxage as Yellow
  FROM wob_stock_age 
  WHERE (maxage = a.maxage) AND (totetype='V') 
  --GROUP BY Date
 )YellowTable
 OUTER APPLY(
 SELECT maxage AS Pink
  FROM wob_stock_age 
  WHERE (maxage = a.maxage) AND (totetype='N') 
 --GROUP BY Date
 )PinkTable
    回复于

相关问题