sql—使用信息单元格而不是列标题

sqserrrh 于 2021-07-26 发布在 Java

关注(0)|答案(3)|浏览(398)

我在报告信息和构建视图时遇到问题我的表信息如下

数据库

表stu

---------------------------------------------------
   ----------------------< Stu >----------------------
   ---------------------------------------------------
   | id | name  | age|id_Orientation | id_StudyStatus|
   ---------------------------------------------------
   | 1  |  John | 24 |       1       |       1       |
   | 2  |  Sara | 23 |       1       |       2       |
   | 3  |  Mary | 26 |       1       |       3       |
   | 4  |  Jax  | 25 |       2       |       1       |
   | 5  | izable| 22 |       2       |       2       |
   | 6  |  Tari | 23 |       2       |       3       |
   | 7  |  Kap  | 26 |       3       |       1       |
   | 8  |  Lio  | 25 |       3       |       2       |
   | 9  |  Soti | 22 |       3       |       3       |
   ---------------------------------------------------

表studystatus

-----------------
   --<StudyStatus>--
   -----------------
   | id |   name   |
   -----------------
   | 1  | Studying |
   | 2  |  Cancel  |
   | 3  |laying off|
   -----------------

工作台方向

-------------------
   ---<Orientation>---
   -------------------
   | id |   name     |
   -------------------
   | 1  |     IT     |
   | 2  |Construction|
   | 3  |   Medical  |
   -------------------

[我最终想要的输出]

----------------------------------------------------------
   ----------------------< Stu >------------------------------
   -----------------------------------------------------------
   | id |    name    |  All  | Studying | Cancel | layingOff |
   ---------------------------------------------------
   | 1  |     IT     |   3   |     1    |    1   |     1     |
   | 2  |Construction|   3   |     1    |    1   |     1     |
   | 3  |   Medical  |   3   |     1    |    1   |     1     |
   -----------------------------------------------------------

通过什么查询？
请帮忙！谢谢

sql sql-server pivot sql-server-2008

来源：https://stackoverflow.com/questions/62441459/use-information-cells-instead-of-column-heads

3条答案

按热度按时间

vh0rcniy1#

试试看。但是首先从学习状态的名称中删除左右侧的空白区域。

select isnull([Studying],0)+isnull([Cancel],0)+isnull([Laying off],0)   [All],  Orientation_Name,[Studying],[Cancel],[Laying off] 
from
(
select s.id,o.Name Orientation_Name ,ss.Name  StudyStatus_Name
 from Stu s inner join StudyStatus ss  on s.ID_StudyStatus=ss.ID inner join Orientation O on o.Id=s.ID_Orientation)as st
 pivot
 (
 count(id)
 FOR StudyStatus_Name   IN ([Studying],[Cancel],[laying off])
) as PV

赞(0）回复(0）举报 2021-07-26

rm5edbpk2#

你可以求条件和得到结果。

SELECT o.name,   
       COUNT(*) AS All, 
       SUM(CASE WHEN ss.Name = 'Studying' THEN 1 ELSE 0 END) AS Studying,   
       SUM(CASE WHEN ss.Name = 'Cancel' THEN 1 ELSE 0 END) AS cancel,
       SUM(CASE WHEN ss.Name = 'Laying Off' THEN 1 ELSE 0 END) AS LayingOff
FROM Orientation as o
inner join Stu as s
on s.id_orientation = s.id
inner join StudyStatus AS ss
on ss.id = s.id_StudyStatus
GROUP BY o.name

赞(0）回复(0）举报 2021-07-26

rjee0c153#

select isnull([Studying], 0) + isnull([Cancel], 0) + isnull([laying off], 0) [All],
    Orientation_Name,
    [Studying],
    [Cancel],
    [laying off]
from (
        select s.id,
            o.name Orientation_Name,
            ss.name StudyStatus_Name
        from Stu s
            inner join StudyStatus ss on ss.id = s.id_StudyStatus
            inner join Orientation o on o.id = s.id_Orientation
    ) as st pivot (
        count(id) FOR StudyStatus_Name IN ([Studying], [Cancel], [laying off])
    ) as PV

注意大小写字母移动的内部连接
感谢@liaqatkundi和@dalek

赞(0）回复(0）举报 2021-07-26

我来回答

sql—使用信息单元格而不是列标题

数据库

[我最终想要的输出]

3条答案

相关问题

热门标签

最新问答