مشکل در نوشتن یک کوئری

[yalameh]

سلام دوستان.
من یه کوئری از جدول زیر می خوام بنویسم و مشکل دارم . لطفا راهنمائی کنید :

فیلد 2 فیلد 1
x 1
y 1
x 1
z 0
z 1
y 1
x 0
x 1
z 0
y 1
x 1

خروچی دلخواه من :

تعداد یک تعداد صفر فیلد 1
x 1 4
y 0 3
z 2 1

[رضا عربلو]

SELECT f1, f2 , COUNT(*) AS c FRom Table1 GROUP BY f1, f2

[محمد سلیم آبادی]

سلام دوست عزیزم.
برای نوشتن این کوئری دو راه وجود دارد. روش اصلی و صحیح این کار استفاده از PIVOT است که هم ساده تر است و هم سرعت اجرای آن بسیار بالاتر از
روش معمولی است.

فیلد 1 = Name
فیلد 2= Value

روش معمولی.



-- Msalim 1
SELECT name AS [Name],
(CASE WHEN [0] IS NULL THEN 0 ELSE [0] END) AS [Number Of 0s],
(CASE WHEN [1] IS NULL THEN 0 ELSE [1] END) AS [Number Of 1s]
FROM (
SELECT D1.name,
(SELECT COUNT(*) FROM t1 WHERE name=D1.name and value=0 GROUP BY name) AS [0] ,
(SELECT COUNT(*) FROM t1 WHERE name=D1.name and value=1 GROUP BY name) AS [1]
FROM (
SELECT DISTINCT name
FROM t1
) AS D1
) AS D2
ORDER BY name


روش استفاده از PIVOT


-- Msalim 2
SELECT name as 'Name',
[0] AS [Number Of 0s],
[1] AS [Number Of 1s]
FROM ( SELECT value,name
FROM t1
) s
PIVOT
(
COUNT(value)
FOR value IN ([0],[1])
)as d
ORDER BY name

[محمد سلیم آبادی]

سلام.

روش معمولی(روش اول) را بدون استفاده از sub query جلوی SELECT می توانید ایجاد کنید.
اپتدا در1 Derived Table ستون "تعداد صفرها" را بدست می آورید و در Derived Table 2ستون "تعداد یکها" را بدست می آورید و در نهایت این دو جدول را با یکدیگر FULL OUTER JOIN می کنید.
و برای اینکه در Result به جای NULL صفر (0) قرار گیرد از CASE استفاده می کنیم.



SELECT Name ,
(CASE WHEN [0] IS NULL THEN 0 ELSE [0] END) AS [Number Of 0s],
(CASE WHEN [1] IS NULL THEN 0 ELSE [1] END) AS [Number Of 1s]
FROM (
SELECT (CASE WHEN d1.Name IS NULL THEN d2.Name ELSE d1.Name END) AS [Name] ,[0], [1]
FROM
(
SELECT Name, COUNT(*) AS [0]
FROM t1
WHERE Value=0
GROUP BY Name
) AS d1
FULL OUTER JOIN
(
SELECT Name, COUNT(*) AS [1]
FROM t1
WHERE Value=1
GROUP BY Name
) AS d2 ON d1.Name=d2.Name
) AS dd

[yalameh]

سلام دوست عزیزم.
برای نوشتن این کوئری دو راه وجود دارد. روش اصلی و صحیح این کار استفاده از PIVOT است که هم ساده تر است و هم سرعت اجرای آن بسیار بالاتر از
روش معمولی است.

فیلد 1 = Name
فیلد 2= Value

روش معمولی.



-- Msalim 1
SELECT name AS [Name],
(CASE WHEN [0] IS NULL THEN 0 ELSE [0] END) AS [Number Of 0s],
(CASE WHEN [1] IS NULL THEN 0 ELSE [1] END) AS [Number Of 1s]
FROM (
SELECT D1.name,
(SELECT COUNT(*) FROM t1 WHERE name=D1.name and value=0 GROUP BY name) AS [0] ,
(SELECT COUNT(*) FROM t1 WHERE name=D1.name and value=1 GROUP BY name) AS [1]
FROM (
SELECT DISTINCT name
FROM t1
) AS D1
) AS D2
ORDER BY name


روش استفاده از PIVOT


-- Msalim 2
SELECT name as 'Name',
[0] AS [Number Of 0s],
[1] AS [Number Of 1s]
FROM ( SELECT value,name
FROM t1
) s
PIVOT
(
COUNT(value)
FOR value IN ([0],[1])
)as d
ORDER BY name


ممنون از جوابتون .
من از روش اول تونستن جواب بگیرم ولی روش Pivot را error میده .
خوبه اسم واقعی فیلدهام را بهت بگم
نام جدول : View_Current
نام فیلد مرجع : LocationCode
نام فیلد 0 و 1 : ObserveCheck

[محمد سلیم آبادی]

لطفا این Script را اجرا کنید و خواهید دید که همان جوابی را می دهد که در پست اول مطرح کردید.


اگر می خواهید بطور شفاف تر مساله حل شود لطفا اطلاعات بیشتری راجب جدول نوع ستون ها ایندکس ها و ... بدهید.


ستون LocationCode را از نوع کاراکتر انتخاب کردم( طبق داده های پست اول) و ستون ObserveCheck را از نوع بیت


انتخاب کردم چون تنها مقادیر 0 و یک را خواهد گرفت.





IF OBJECT_ID (N'View_Current') IS NULL
CREATE TABLE View_Current
(
LocationCode CHAR,
ObserveCheck BIT
)

IF NOT EXISTS (SELECT * FROM View_Current)
INSERT INTO View_Current
SELECT LocationCode='X', ObserveCheck=1
UNION ALL
SELECT 'Y',1
UNION ALL
SELECT 'X',1
UNION ALL
SELECT 'Z',0
UNION ALL
SELECT 'Z',1
UNION ALL
SELECT 'Y',1
UNION ALL
SELECT 'X',0
UNION ALL
SELECT 'X',1
UNION ALL
SELECT 'Z',0
UNION ALL
SELECT 'Y',1
UNION ALL
SELECT 'X',1


-- MSALIM 1
SELECT LocationCode,
(CASE WHEN [0] IS NULL THEN 0 ELSE [0] END) AS [Number Of 0s],
(CASE WHEN [1] IS NULL THEN 0 ELSE [1] END) AS [Number Of 1s]
FROM (
SELECT D1.LocationCode,
(SELECT COUNT(*)
FROM View_Current
WHERE LocationCode=D1.LocationCode
AND ObserveCheck=0
GROUP BY LocationCode) AS [0] ,
(SELECT COUNT(*)
FROM View_Current
WHERE LocationCode=D1.LocationCode
AND ObserveCheck=1
GROUP BY LocationCode) AS [1]
FROM (
SELECT DISTINCT LocationCode
FROM View_Current
) AS D1
) AS D2

-- MSALIM 2
SELECT LocationCode ,
[0] AS [Number Of 0s],
[1] AS [Number Of 1s]
FROM ( SELECT ObserveCheck ,LocationCode
FROM View_Current
) s
PIVOT
(
COUNT(ObserveCheck)
FOR ObserveCheck IN ([0],[1])
)as d


-- MSALIM 3
SELECT LocationCode ,
(CASE WHEN [0] IS NULL THEN 0 ELSE [0] END) AS [Number Of 0s],
(CASE WHEN [1] IS NULL THEN 0 ELSE [1] END) AS [Number Of 1s]
FROM (
SELECT (CASE WHEN d1.LocationCode IS NULL
THEN d2.LocationCode
ELSE d1.LocationCode END) AS [LocationCode],
[0], [1]
FROM
(
SELECT LocationCode, COUNT(*) AS [0]
FROM View_Current
WHERE ObserveCheck=0
GROUP BY LocationCode
) AS d1
FULL OUTER JOIN
(
SELECT LocationCode, COUNT(*) AS [1]
FROM View_Current
WHERE ObserveCheck=1
GROUP BY LocationCode
) AS d2 ON d1.LocationCode=d2.LocationCode
) AS dd

[yalameh]

SELECT LocationCode, [0] AS [Number Of 0s], [1] AS [Number Of 1s]
FROM (SELECT ObserveCheck, LocationCode
FROM View_Current) s PIVOT(COUNT(ObserveCheck) FOR ObserveCheck IN ([0], [1])) AS d

دوست عزیز msalim
وقتی من کوئری فوق را اجرا می کنم پیغام زیر نشان داده می شود :
line 1 : Incorrect syntax near 'PIVOT'

[محمد سلیم آبادی]

از چه ورژن SQL استفاده می کنید؟ (من این کوئری را در 2005 نوشتم و هیچ پیغام خطایی هم نمی دهد!)
از نوع پیغام خطایتان می توان تشخیص داد که از SQL 2000 استفاده می کنید.

[yalameh]

بله . ورژن اس کیو ال من 2000 است . احتمالا دستور Pivot را نمی شناسه .
یه مورد دیگه از کوئری : اگه خواسته باشیم فیلدهای دیگری از تیبل View_Current از قبیل مثلا LocationID , AreaName در لیست کوئری بیایند چکار باید کرد :

SELECT LocationCode ,
(CASE WHEN [0] IS NULL THEN 0 ELSE [0] END) AS [Number Of 0s],
(CASE WHEN [1] IS NULL THEN 0 ELSE [1] END) AS [Number Of 1s]
FROM (
SELECT (CASE WHEN d1.LocationCode IS NULL
THEN d2.LocationCode
ELSE d1.LocationCode END) AS [LocationCode],
[0], [1]
FROM
(
SELECT LocationCode, COUNT(*) AS [0]
FROM View_Current
WHERE ObserveCheck=0
GROUP BY LocationCode
) AS d1
FULL OUTER JOIN
(
SELECT LocationCode, COUNT(*) AS [1]
FROM View_Current
WHERE ObserveCheck=1
GROUP BY LocationCode
) AS d2 ON d1.LocationCode=d2.LocationCode
) AS dd

[محمد سلیم آبادی]

لطفا یک نمونه از داده های جدولتان و یک نمونه از نتیجه مورد نظرات پست کنید.

(همیشه از تگ CODE برای پست کردن Query استفاده کنید. البته اگر مستقیما از SQL می خواهید کپی کنید اول در Word کپی پیست کرده سپس رنگشو مشکی کنید بعد به اینجا پست کنید.)

[yalameh]

این هم یه نمونه ای از خروجی کوئری .

[محمد سلیم آبادی]

این که همون داده های پست اول است. ستونی بهش اضافه نشده است!

به هر حال بهترین روش بر این کار را سرانجام بدست آوردم.

لطفا یک نگاهی به کدهای زیر بکنید.

کافی است که این کد ها را در یک پنجره کوئری کوپی کرده و اجرا کنید (execute)

توضیح: ابتدا یک متغیر جدولی ایجاد شده و با چند سطر نمونه پر شده و ...





DECLARE @Sample TABLE
(
LocationCode CHAR(1) NOT NULL,
ObserveCheck TINYINT NOT NULL
)

INSERT @Sample
SELECT 'X', 1 UNION ALL
SELECT 'X', 1 UNION ALL
SELECT 'X', 1 UNION ALL
SELECT 'X', 1 UNION ALL
SELECT 'X', 0 UNION ALL
SELECT 'Y', 1 UNION ALL
SELECT 'Y', 1 UNION ALL
SELECT 'Y', 1 UNION ALL
SELECT 'Z', 1 UNION ALL
SELECT 'Z', 0 UNION ALL
SELECT 'Z', 0




SELECT LocationCode,
SUM(CASE WHEN ObserveCheck = '0' THEN 1 ELSE 0 END) AS [0],
SUM(CASE WHEN ObserveCheck = '1' THEN 1 ELSE 0 END) AS [1]
FROM @Sample
GROUP BY LocationCode
ORDER BY LocationCode

[محمد سلیم آبادی]

اگر در ستون ObserveCheck تنها 0 و 1 ذخیره می شود بهترین روش، روش زیر خواهد بود!


ولی اگر بخواهید در این ستون مقادیری شبیه به مثلا 2، 3 و .. یا A ،B و... قرار دهید این روش کار نخواهد کرد و باید از روش پست قبلی استفاده کنید.




SELECT LocationCode,
SUM(1- ObserveCheck) AS [0],
SUM(ObserveCheck) AS [1]
FROM @Sample
GROUP BY LocationCode
ORDER BY LocationCode


SELECT LocationCode,
COUNT(*) - SUM(ObserveCheck) AS [0],
SUM(ObserveCheck) AS [1]
FROM @Sample
GROUP BY LocationCode
ORDER BY LocationCode