نوشتن یک query

[danial82]

با سلام خدمت دوستان
فکر کنم همه با دیتا بیس pubs آشنا هستند تو نوشتن query دچار مشکل شدم

میخوام بدونم هر فروشگاه بیشترین نوع کتابی که فروخته از لحاظ مبلغ چی بوده؟



SELECT stores.stor_name , titles.type , SUM(titles.price) AS Expr3
FROM sales INNER JOIN
stores ON sales.stor_id = stores.stor_id INNER JOIN
titles ON sales.title_id = titles.title_id
GROUP BY stores.stor_name, titles.type
ORDER BY stores.stor_name


تا اینجاش که ساده است بعدشو چه کنیم؟

[danial82]

یعنی کسی جواب اینو نمیدونه ؟
اگه یه راهنمایی هم کنید ممنون میشم

[nasseritemp]

سلام.
Query زير مشكل شما رو حل ميكنه. اما ساختار ديتابيس طوري نيست كه عنوان رو بشه نشون داد.
يعني فقط نام مغازه و بيشترين قيمت نوعي كه بيشترين فروش رو در هر مغازه داشته رو ميده.


SELECT stor_id, MAX(Price) AS MaxPrice
FROM (SELECT TOP 100 PERCENT dbo.sales.stor_id, dbo.titles.type, SUM(dbo.sales.qty * dbo.titles.price) AS Price
FROM dbo.sales INNER JOIN
dbo.titles ON dbo.sales.title_id = dbo.titles.title_id
GROUP BY dbo.sales.stor_id, dbo.titles.type
ORDER BY dbo.sales.stor_id, dbo.titles.type) AS derivedtbl_1
GROUP BY stor_id

[danial82]

سلام.
Query زير مشكل شما رو حل ميكنه. اما ساختار ديتابيس طوري نيست كه عنوان رو بشه نشون داد.
يعني فقط نام مغازه و بيشترين قيمت نوعي كه بيشترين فروش رو در هر مغازه داشته رو ميده.


SELECT stor_id, MAX(Price) AS MaxPrice
FROM (SELECT TOP 100 PERCENT dbo.sales.stor_id, dbo.titles.type, SUM(dbo.sales.qty * dbo.titles.price) AS Price
FROM dbo.sales INNER JOIN
dbo.titles ON dbo.sales.title_id = dbo.titles.title_id
GROUP BY dbo.sales.stor_id, dbo.titles.type
ORDER BY dbo.sales.stor_id, dbo.titles.type) AS derivedtbl_1
GROUP BY stor_id



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

بیشتر توضیح بدی

[Afshin160]

سلام..
ظاهر queryكه مشكلي نداره ولي
توضيحات كافي نيست ...
اول tables , columns , رابطه ها و pk را مشخص كنيد ....تا بشه روش كار كرد ...

[danial82]

سلام..
ظاهر queryكه مشكلي نداره ولي
توضيحات كافي نيست ...
اول tables , columns , رابطه ها و pk را مشخص كنيد ....تا بشه روش كار كرد ...

دوست عزیز این کوئری برای دیتابیس pubs میباشد اگه یه نگاهی بکنی متوجه رابطه ها و ستونها میشی
بازم از توجهت ممنونم
یعنی کسی جواب این کوئری رو نمیدونه

[رضا جاسبی]

فکر کنم بهتره سوالت رو مرحله به مرحله جلو بریم
میخوام بدونم هر فروشگاه بیشترین نوع کتابی که فروخته از لحاظ مبلغ چی بوده؟
1. در PUB و در جدول Sale یک فروشگاه در یک فاکتور می تواند چندین کتاب بفروشد و همینطور از یک کتاب در چندین فاکتور فروش داشته باشد. ( تعداد مورد نظرم نیست. بلکه سطر DB رو میگم) در واقع کلید این جدول ترکیب سه فیلد است : شماره فروشگاه ، شماره فاکتور و شماره کتاب
2. آیا شما می خواهی فاکتور رو دخالت بدی یا اصولا کاری به فاکتور نداری و کل فروش رو می خواهی. البته می تونی روی Ord_Date فیلتر بذاری.
3. آیا بحث مبلغ یعنی تعداد موجود در سفارش ضربدر قیمت رو دارید یا فقط قیمت و یا فقط تعداد. منظورم برای انتخاب "بیشترین" که در سوال گفتید.
4. در حقیقت آیا سوال شما این است که بیشترین میزان فروش هر فروشگاه از چه کتابی و به چه مبلغی بوده؟ در این صورت و در صورتی که پاسخ شما به سوال دوم این باشه که فاکتور مهم نیست پس اول باید تعداد کل فروخته شده توسط هر فروشگاه رو مشخص کنید:


SELECT stor_id, title_id, SUM(qty) AS SumSales
FROM dbo.sales
GROUP BY stor_id, title_id

البته چون در اطلاعات فعلی یک فروشگاه بیش از یک فاکتور با محتوای یک کتاب خاص نداشته و به عبارتی هر کتاب رو فقط یکبار فروخته اگر sum هم نمی آوردیم فرقی نمی کرد. اما در حالت کلی می خواهیم.
5. حالا باید این جدول جدید رو با دو جدول Store و Title ترکیب یا Join کنی :



SELECT SalesTable.Stor_id, dbo.stores.stor_name, SalesTable.SumSales,
dbo.titles.price, SalesTable.Title_id, dbo.titles.title
FROM

(SELECT Stor_id, Title_id, SUM(Qty) AS SumSales
FROM Sales
GROUP BY Stor_id, Title_id)
SalesTable


INNER JOIN
dbo.titles ON SalesTable.Title_id = dbo.titles.title_id INNER JOIN
dbo.stores ON SalesTable.Stor_id = dbo.stores.stor_id

قسمت میانی این تکه کد همون کد قبلیه که در یک جدول مجازی ریختم.
نتیجه این Select میشه یک جدول شامل نام فروشگاه ، نام کتاب ، قیمت هر کتاب ، تعداد فروخته شده. که البته دو تا ID هم داریم که برش می داریم.
6. یک نکته مهم اینه که میشد همین کوئری رو به صورتی دیگه هم نوشت. یعنی جدولها رو اول ترکیب کرد بعد Sum حساب کرد. ایرادش در حالتی مشخص میشه که جدولها بزرگ باشن و اون نکته ای که در 4 گفتم. یعنی هر فروشگاه از یک کتاب با چندین فاکتور مختلف فروخته باشد. ولی الان فرقی نمیکنه :



SELECT dbo.stores.stor_name, dbo.titles.title,
SUM(dbo.sales.qty * dbo.titles.price) AS totalSold
FROM dbo.sales INNER JOIN
dbo.stores ON dbo.sales.stor_id = dbo.stores.stor_id INNER JOIN
dbo.titles ON dbo.sales.title_id = dbo.titles.title_id
GROUP BY dbo.stores.stor_name, dbo.titles.title

یعنی همون کدی که دوستمون نوشتن.

7. حالا شما می خواهی بیشترین مبلغ فروش هر فروشگاه رو داشته باشی. یعنی هر فروشگاه از یک نوع کتاب بیشترین مبلغ فروش رو داشته و می خوای اون سطر رو داشته باشی. اینجا یک کمی پیچیده تره. باید بگی اون سطری رو می خوام از این جدول که بیشترین میزان فروش رو داشته باشه. اگر بخوای از MAX و Group By استفاده کنی مثل اون چیزی که دوستمون گفته امکان داشتن عنوان کتاب از بین میره. باید بیشترین مبلغ رو برای هر فروشگاه بدست بیاری و بعد بگی اون سطری رو نشون بده که میزان فروشش مساوی حداکثر مقداره :


SELECT stor_name, title, totalSold
FROM


(SELECT stor_name, title, SUM(sales.qty * titles.price) AS totalSold
FROM sales INNER JOIN
stores ON sales.stor_id = stores.stor_id INNER JOIN
titles ON sales.title_id = titles.title_id
GROUP BY stores.stor_name, titles.title) Tbl1

WHERE (totalSold =
(SELECT MAX(TotalSold)
FROM


(SELECT stores.stor_name,titles.title,
SUM(sales.qty * titles.price) AS totalSold
FROM sales INNER JOIN
stores ON sales.stor_id = stores.stor_id INNER JOIN
titles ON sales.title_id = titles.title_id
GROUP BY stores.stor_name, titles.title) Tbl2


WHERE Tbl1.stor_name = Tbl2.stor_name))



این تیکه آخر یک کمی پیچیده است. من مخصوصا تیکه مربوط به جدول اصلی رو که در 6 بدست اومده رو جدا کردم. البته همانطور که گفتم بهتره از جدول 5 استفاده کنیم تا کد بهینه تر باشه.

جواب هم میشه :



Bookbeat But Is It User Friendly? 918
Doc-U-Mat: Quality Laundry and Books Computer Phobic AND Non-Phobic Individuals: Behavior Variations 431.8
News & Brews Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 838
Barnum's Secrets of Silicon Valley 1000
Fricative Bookshop Straight Talk About Computers 299.85
Eric the Read Books The Busy Executive's Database Guide 99.95



چاکریم. تشکر لازم نیست.

[danial82]

رضا جاسبی جان دمت گرم گل کاشتی
اگه کوئری پیچیده ای مثل این بنظرت رسید خوشحال میشم بگی روش فکر کنم

[رضا جاسبی]

رضا جاسبی جان دمت گرم گل کاشتی
اگه کوئری پیچیده ای مثل این بنظرت رسید خوشحال میشم بگی روش فکر کنم

کاری نکردم دادا.
من فکر می کنم که بهتره خودت کوئری هایی طراحی کنی و بعد جواب مناسبش رو پیدا کنی. اینطوری چیزی طرح نمی کنی که خودت نتونی حلش کنی. بعد کم کم ذهنت آماده میشه که کوئری های سختتر رو حل کنی. ولی خوب برای اینکه روی ماهتو زمین نندازم: (از NorthWind استفاده می کنیم)
برای هر فرد بیشترین مبلغ فروش و حنس فروخته شده (یا سفارش داده شده) رو پیدا کن.
خودم هم کدش رو ندارم و اگر لازم باشه باید بنویسم.
سعی کن مرحله به مرحله حل کنی.

[رضا جاسبی]

آقا دانیال بی خیال شدی؟ با اون شور و شوقی که نشون دادی گفتم رفتی تا ته خط پایگاه داده ها بتازونی. مجبور شدم این تاپیک رو از گنجه خاطره ها بیرون بیارم.

[danial82]

رضا جان سلام کوئری همون روز نوشتم ولی این چند وقت خیلی سرم شلوغ بود وقت نکردم پست بزنم اگه بازم کوئری پیچید داری بزار اگه نه من بزارم روش فکر کنی



SELECT FullName,TotalSales
FROM
(SELECT FirstName + ' ' + LastName AS FullName, [Order Details].UnitPrice * Quantity AS TotalSales
FROM Employees INNER JOIN
Orders ON Employees.EmployeeID = Orders.EmployeeID INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN
Products ON [Order Details].ProductID = Products.ProductID
) AS TBL1
where TotalSales=(SELECT max(TotalSales)
FROM (SELECT FirstName + ' ' + LastName AS FullName, [Order Details].UnitPrice * Quantity AS TotalSales FROM Employees INNER JOIN
Orders ON Employees.EmployeeID = Orders.EmployeeID INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN
Products ON [Order Details].ProductID = Products.ProductID
) AS TBL2
where (TBL2.FullName=TBL1.FullName)
)

[salimim]

فكر كنم
INNER JOIN
Products ON [Order Details].ProductID = Products.ProductID
اضافه باشه

ممنون مي شم اگه كسي براي northwind مثالهايي مثل اين مطرح كنه

[danial82]

فكر كنم
INNER JOIN
Products ON [Order Details].ProductID = Products.ProductID
اضافه باشه

ممنون مي شم اگه كسي براي northwind مثالهايي مثل اين مطرح كنه

درسته میشه حذفش کرد


یک کوئری برای pubs
از جدول authors بیشترین state پیدا کنید و تمام state ها را برابر آن قرار دهید
مثلا state ، CA 15 بار تکرار شده میخواهیم state تمام نویسندگان رو برابر آن قرار دهیم
* توجه داشته باشید که با یک کوئری این عمل را انجام دهید

[salimim]

از لطفتون ممنونم

update authors
set state =(select top 1 state from authors
group by state
order by count(state) desc)



اگه پاسخ بهتري با performance بالاتر وجود دارد ( كه حتما همينطور هست)
راهنمايي بفرماييد

[danial82]

یک کوئری دیگه
جدولی دارم که ورود افراد بطور روزانه توش ثبت میکنم چطور میتوانم آخرین ورود افراد رو داشته باشم
ممکن یکی آخرین ورودش هفته پیش بوده باشه ممکنه آخرین نفر ورودش یک ساعت پیش بوده باشه
با تشکر

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

یک کوئری دیگه
جدولی دارم که ورود افراد بطور روزانه توش ثبت میکنم چطور میتوانم آخرین ورود افراد رو داشته باشم
ممکن یکی آخرین ورودش هفته پیش بوده باشه ممکنه آخرین نفر ورودش یک ساعت پیش بوده باشه
با تشکر

در حقیقت این مساله دقیقا مشابه مساله عمومی معروف "the Most Recent Order for Each Employee " است.

که حداقل 5 روش شناخته شده برای حل این مساله وجود داره که 2 تای آن را در اینجا ذکر می کنم.
با APLLY و JOIN و ... هم میشه حلش کرد.

query ها را بر اساس جدول زیر ایجاد کردم


declare @customer table (id int, cusid int, date date, unique(id))
insert @customer values (1, 1, '2005/5/5'), (2, 1,'2006/4/4'), (3, 1,'2007/7/7'),
(4, 2,'2008/6/6'), (5, 2,'2008/7/7')



--Solution 1
SELECT *
FROM @customer AS C1
WHERE id =
(SELECT TOP(1) id
FROM @customer AS C2
WHERE C2.cusid = C1.cusid
ORDER BY Date DESC, ID DESC);

--Solution 2
SELECT *
FROM (SELECT *,
ROW_NUMBER() OVER(PARTITION BY cusid
ORDER BY Date DESC, ID DESC) AS RowNum
FROM @customer) AS D
WHERE RowNum = 1;