복붙노트

[SQL] 나는 크로스 탭 쿼리를 만드는 방법을 알 필요가

SQL

나는 크로스 탭 쿼리를 만드는 방법을 알 필요가

나는 도움이 결과 아래를 만들 필요합니다. 나는 SQL 피벗 생각하지만 난 그것을 사용하는 방법을 모르겠어요. 몇 가지 예를 쳐다 보면서 해결책을 제공 할 수 없다. 이 작업을 수행하는 방법에 대한 다른 아이디어도 환영합니다. 상태 열은 동적으로 생성해야합니다.

세 개의 테이블, 자산, assettypes, assetstatus 되세요

Table: assets
assetid     int
assettag    varchar(25)
assettype   int
assetstatus int

Table: assettypes
id         int
typename   varchar(20)  (ex: Desktop, Laptop, Server, etc.)

Table: assetstatus
id         int
statusname varchar(20)  (ex: Deployed, Inventory, Shipped, etc.)

원하는 결과 :

AssetType     Total   Deployed   Inventory  Shipped     ...
-----------------------------------------------------------
Desktop         100       75        20          5       ...
Laptop           75       56        19          1       ...
Server           60       50        10          0       ...

일부 데이터 :

assets table:
1,hol1234,1,1
2,hol1233,1,2
3,hol3421,2,3
4,svr1234,3,1

assettypes table:
1,Desktop
2,Laptop
3,Server

assetstatus table:
1,Deployed
2,Inventory
3,Shipped

해결법

  1. ==============================

    1.변환이 유형의 피봇 불린다. 당신은 내가 SQL 서버와 MySQL에 대한 답변을 제공 할 것입니다, 그래서 당신이 사용하고있는 데이터베이스를 지정하지 않았습니다.

    변환이 유형의 피봇 불린다. 당신은 내가 SQL 서버와 MySQL에 대한 답변을 제공 할 것입니다, 그래서 당신이 사용하고있는 데이터베이스를 지정하지 않았습니다.

    SQL 서버 : SQL Server를 사용하는 경우 2005 + 피벗 기능을 구현할 수 있습니다.

    당신은 당신이 열로 변환하려는 값의 알려진 번호가있는 경우 당신은 하드 코드 쿼리를 할 수 있습니다.

    select typename, total, Deployed, Inventory, shipped
from
(
  select count(*) over(partition by t.typename) total,
    s.statusname,
    t.typename
  from assets a
  inner join assettypes t
    on a.assettype = t.id
  inner join assetstatus s
    on a.assetstatus = s.id
) d
pivot
(
  count(statusname)
  for statusname in (Deployed, Inventory, shipped)
) piv;

    데모와 SQL 바이올린을 참조하십시오.

    이 상태 값의 알 수없는 번호가 경우에, 당신은 런타임에서 열 목록을 생성하는 동적 SQL을 사용해야합니다.

    DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(statusname) 
                    from assetstatus
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT typename, total,' + @cols + ' from 
             (
                select count(*) over(partition by t.typename) total,
                  s.statusname,
                  t.typename
                from assets a
                inner join assettypes t
                  on a.assettype = t.id
                inner join assetstatus s
                  on a.assetstatus = s.id
            ) x
            pivot 
            (
                count(statusname)
                for statusname in (' + @cols + ')
            ) p '

execute(@query)

    데모와 SQL 바이올린을 참조하십시오

    이것은 또한 케이스 식 집계 함수를 사용하여 작성 될 수있다 :

    select typename,
  total,
  sum(case when statusname ='Deployed' then 1 else 0 end) Deployed,
  sum(case when statusname ='Inventory' then 1 else 0 end) Inventory,
  sum(case when statusname ='Shipped' then 1 else 0 end) Shipped
from
(
  select count(*) over(partition by t.typename) total,
    s.statusname,
    t.typename
  from assets a
  inner join assettypes t
    on a.assettype = t.id
  inner join assetstatus s
    on a.assetstatus = s.id
) d
group by typename, total

    데모와 SQL 바이올린을 참조하십시오

    MySQL은 : 당신이 집계 함수와 CASE 식을 사용해야합니다 그래서이 데이터베이스는 피벗 기능이 없습니다. 당신이 다음에 약간 쿼리를 변경해야합니다, 그래서 그것은 또한, 기능을 윈도가 없습니다 :

    select typename,
  total,
  sum(case when statusname ='Deployed' then 1 else 0 end) Deployed,
  sum(case when statusname ='Inventory' then 1 else 0 end) Inventory,
  sum(case when statusname ='Shipped' then 1 else 0 end) Shipped
from
(
  select t.typename,
    (select count(*) 
     from assets a1 
     where a1.assettype = t.id 
     group by a1.assettype) total,
    s.statusname
  from assets a
  inner join assettypes t
    on a.assettype = t.id
  inner join assetstatus s
    on a.assetstatus = s.id
) d
group by typename, total;

    데모와 SQL 바이올린을 참조하십시오

    당신이 MySQL의에서 동적 솔루션을 필요로하는 경우 그런 다음 실행하는 SQL 문자열을 생성하는 준비된 문을 사용해야합니다 :

    SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN statusname = ''',
      statusname,
      ''' THEN 1 else 0 END) AS `',
      statusname, '`'
    )
  ) INTO @sql
FROM assetstatus;

SET @sql 
  = CONCAT('SELECT typename,
              total, ', @sql, ' 
            from
            (
              select t.typename,
                (select count(*) 
                 from assets a1 
                 where a1.assettype = t.id 
                 group by a1.assettype) total,
                s.statusname
              from assets a
              inner join assettypes t
                on a.assettype = t.id
              inner join assetstatus s
                on a.assetstatus = s.id
            ) d
            group by typename, total');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

    데모와 SQL 바이올린을 참조하십시오.

    결과는 모두 데이터베이스의 모든 쿼리에 대해 동일합니다 :

    | TYPENAME | TOTAL | DEPLOYED | INVENTORY | SHIPPED |
-----------------------------------------------------
|  Desktop |     2 |        1 |         1 |       0 |
|   Laptop |     1 |        0 |         0 |       1 |
|   Server |     1 |        1 |         0 |       0 |
  2. ==============================

    2.비 피벗 준수 DBMS를 (절대 데이터베이스) 나는이 SQL 크로스 탭 동등한 문을 사용하여 더 많은 성공 사용 :

    비 피벗 준수 DBMS를 (절대 데이터베이스) 나는이 SQL 크로스 탭 동등한 문을 사용하여 더 많은 성공 사용 :

    SELECT
  sub.TypeName
, SUM(sub.[Count]) AS "Total"
, SUM(CASE WHEN AssetStatus='1' THEN sub.[Count] ELSE 0 END) AS "Deployed"
, SUM(CASE WHEN AssetStatus='2' THEN sub.[Count] ELSE 0 END) AS "Inventory"
, SUM(CASE WHEN AssetStatus='3' THEN sub.[Count] ELSE 0 END) AS "Shipped"
FROM
 (
SELECT
  t.TypeName
, AssetStatus
, COUNT(AssetID) AS "Count"
FROM
  Assets
  JOIN AssetTypes t ON t.ID = AssetType
  JOIN AssetStatus s ON s.ID = AssetStatus
GROUP BY t.TypeName, AssetStatus, s.StatusName
 ) sub
GROUP BY sub.TypeName
;

    내가 깨달았 (위)이 코드는 MySQL을 내 현재의 절대 데이터베이스에로 MySQL에서 동일하게 실행 아래로 내 코드를 적용하여 작동하지 않았다. 그 이유는 관대 주류 데이터베이스에서 허용되지 COUNT (NULL) = 0을 수락 디베이스 역설뿐만 아니라 절대 데이터베이스 함정 피 처리 특정의 NULL이다. 이 믿는 것은 (CASE 처리 ..) 대부분의 데이터베이스에서 잘 실행됩니다 그래서이 내 적응 코드입니다 :

    SELECT
  sub.TypeName
, SUM(sub.AssetCase) AS "Total"
, SUM(CASE WHEN sub.StatusName = 'Deployed' THEN sub.AssetCase ELSE 0 END) AS "Deployed"
, SUM(CASE WHEN sub.StatusName = 'Inventory' THEN sub.AssetCase ELSE 0 END) AS "Inventory"
, SUM(CASE WHEN sub.StatusName = 'Shipped' THEN sub.AssetCase ELSE 0 END) AS "Shipped"
FROM
  (
   SELECT
     c.TypeName
   , c.StatusName
   , CASE WHEN a.AssetID IS NULL THEN 0 ELSE 1 END AS "AssetCase"
   FROM
     (
      SELECT
        t.ID AS tID
      , t.TypeName
      , s.ID AS sID
      , s.StatusName
      FROM
        AssetTypes t, AssetStatus s
     ) c
   LEFT JOIN Assets a
     ON a.AssetType = c.tID AND a.AssetStatus = c.sID
   ) sub
GROUP BY
  sub.TypeName
;

    친애하는 닐스 소년

    3. from https://stackoverflow.com/questions/15714265/i-need-to-know-how-to-create-a-crosstab-query by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 각 항목에 대한 JPA 선택 최신 인스턴스  (0) 2020.06.09
[SQL] 는 SQL 서버에서 MySQL을 ON DUPLICATE KEY UPDATE의 등가  (0) 2020.06.09
[SQL] SQL 서버하십시오 WHERE 절에 MAX 같은 집계 함수를 사용하는 방법  (0) 2020.06.08
[SQL] MySQL은, 오류 126 : 테이블에 대한 잘못된 키 파일  (0) 2020.06.08
[SQL] NOT이 존재하는 INSERT 값  (0) 2020.06.08
,

티스토리툴바