forodo
دوشنبه 08 دی 1393, 10:11 صبح
سلام
با استفاده از 3 جدول زیر کوئری می زنم که خروجی اش سطر تکراری میاورد.
http://8pic.ir/images/8vn3h65npi9bhly9ouog.png
http://8pic.ir/images/35u5njae9hnx9mcf3hw4.png
http://8pic.ir/images/y2sxyq6sktqg6je6uggw.png
این هم خروجی کوئری با خود کوئری:
http://8pic.ir/images/orpgjsqqlplkj3yv7c5c.png
کوئری عکس بالا:
with tblWith1 as
(
SELECT MAX(tblDaryafti.ID) AS DID, tblLetterVarede.ID AS dd
FROM tblLetterVarede INNER JOIN tblDaryafti
ON tblLetterVarede.ID = tblDaryafti.VaredeLetterID
WHERE tblDaryafti.SendErjaa IS NOT NULL AND tblDaryafti.VaredeLetterID IN (SELECT LetterNumber
FROM tblVaziateMokatebat
WHERE Username = N'3')
GROUP BY tblLetterVarede.ID
)
SELECT tblDaryafti.ID AS DID,tblLetterVarede.ID,tblLetterVarede.LetterID
,Subject,tabaghe,jahat,ErjaDate,ErjaTime,
InOut = N'وارده', (CASE tblDaryafti.Archived WHEN 0 THEN N'جریان' ELSE N'اختتام' END) AS Ekhtetam, ProjectName
, tblVaziateMokatebat.Ersal, tblVaziateMokatebat.Erjaa, tblVaziateMokatebat.Ekhtetam AS Ekhtetam1,'' AS Level
FROM tblLetterVarede INNER JOIN tblDaryafti
ON tblLetterVarede.ID = tblDaryafti.VaredeLetterID INNER JOIN tblWith1
ON tblDaryafti.ID = tblWith1.DID INNER JOIN tblVaziateMokatebat ON tblVaziateMokatebat.LetterNumber = tblLetterVarede.ID
با استفاده از 3 جدول زیر کوئری می زنم که خروجی اش سطر تکراری میاورد.
http://8pic.ir/images/8vn3h65npi9bhly9ouog.png
http://8pic.ir/images/35u5njae9hnx9mcf3hw4.png
http://8pic.ir/images/y2sxyq6sktqg6je6uggw.png
این هم خروجی کوئری با خود کوئری:
http://8pic.ir/images/orpgjsqqlplkj3yv7c5c.png
کوئری عکس بالا:
with tblWith1 as
(
SELECT MAX(tblDaryafti.ID) AS DID, tblLetterVarede.ID AS dd
FROM tblLetterVarede INNER JOIN tblDaryafti
ON tblLetterVarede.ID = tblDaryafti.VaredeLetterID
WHERE tblDaryafti.SendErjaa IS NOT NULL AND tblDaryafti.VaredeLetterID IN (SELECT LetterNumber
FROM tblVaziateMokatebat
WHERE Username = N'3')
GROUP BY tblLetterVarede.ID
)
SELECT tblDaryafti.ID AS DID,tblLetterVarede.ID,tblLetterVarede.LetterID
,Subject,tabaghe,jahat,ErjaDate,ErjaTime,
InOut = N'وارده', (CASE tblDaryafti.Archived WHEN 0 THEN N'جریان' ELSE N'اختتام' END) AS Ekhtetam, ProjectName
, tblVaziateMokatebat.Ersal, tblVaziateMokatebat.Erjaa, tblVaziateMokatebat.Ekhtetam AS Ekhtetam1,'' AS Level
FROM tblLetterVarede INNER JOIN tblDaryafti
ON tblLetterVarede.ID = tblDaryafti.VaredeLetterID INNER JOIN tblWith1
ON tblDaryafti.ID = tblWith1.DID INNER JOIN tblVaziateMokatebat ON tblVaziateMokatebat.LetterNumber = tblLetterVarede.ID