Check the latest 7 price increases. Haha, you can find the solution directly in csdn !!!! Thank you !!!

Difficult to query. Could you please refer to SS _ 328 (zhuyun) 15:49:36 in MS-SQL server/basic questions

The following purchase table is available:

Goods unit purchase date purchase amount

Product 1 unit 1 2004-12-12 50
Product 1 unit 1 60
Product 2 unit 1 2004-12-12 60
Product 2 unit 1 2005-12-12 60
Product 2 Unit 2 2003-12-12 40
..........

The following statistics are made to calculate the latest 7 price advances.

Commodity unit price 1 price 2... price 7
Product 1 Unit 1
Product 2 Unit 1
Product 2 Unit 2
...................................

Processing methods have been implemented by using cursors or client computing, and the speed has not met the requirements (large projects, large customers/large data volumes/complex network conditions)

Can you use the query statement? Please give me some advice. Thank you.
Number of questions: 100, replies: 22top

Lsxaa (Xiao Li pencil knife) on the first floor)Back to 15:57:16 score 0

Select item, unit,
Price 1 = (select top 1 purchase amount
From t C
Where C. Commodity = A. Commodity and C. unit = A. Unit
And (select count (*)
From T B
Where B. Commodity = C. Commodity and B. unit = C. Unit
And
B. purchase date> = C. purchase date) = 1 -- purchase price n write n here
),
...
From t
Group by A. item, A. Unit top

Second floor SS _ 328 (second floor)Back to 15:57:54 score 0

The first step is to filter the purchase table and keep only the last 7 purchase records. How can this problem be achieved?



Product Code unit code
Date price
September 000040000100001000230000700002000052004-02-092580.0
September 000040000100001000230000700002000052004-02-082580.0
September 000040000100001000230000700002000052004-02-082580.0
September 000040000100001000230000700002000052004-02-072580.0
September 000040000100001000230000700002000052004-02-072580.0
September 00004000010000100023000292004-02-062579.0
00004000010000100024000292004-03-145098.0
00004000010000100024000292004-03-065098.0
September 00004000010000100024000292004-02-255097.0
September 00004000010000100024000292004-02-255097.0
September 00004000010000100024000292004-02-255097.0
September 00004000010000100024000292004-02-255097.0
September 00004000010000100024000292004-02-255097.0
00004000010000100025000292004-03-154396.0
00004000010000100025000292004-03-064398.0
Top

Zjcxc (zhujian) on the third floor)Back to 15:59:15 score 0

Does the table have a primary key? Top

Zjcxc (zhujian) on the 4th floor)Back to 15:59:45 score 0

If no primary key exists, the top result cannot be obtained by pressing the first floor.

5th floor SS _ 328 (Hangzhou Branch)Back to 16:05:30 score 0

To lsxaa (Xiao Li pencil knife)

It seems to be a problem. After the execution, the query is still in progress... it hasn't been reflected for a long time. Top

On the 6th floor, SS _ 328 (zhuyun)Back to 16:07:22 score 0

The table has a child growth field primary key top.

Lsxaa on the 7th floor (Xiao Li pencil knife)Back to 16:11:05 score 0

Select id = identity (INT, 1, 1), * into # T from t order by commodity, unit, date DESC

Select item, unit,
Sum (case when id = (select top 1 ID
From t C
When C. Commodity = A. Commodity and C. unit = A. Unit
And (select count (*)
From T B
Where
B. Goods = C. Goods and B. unit = C. Unit
And id <= A. ID) = 1
) Then
Purchase amount else 0 end) as price 1,
...
From t
Group by A. item, A. Unit


Top

Lsxaa on the eighth floor (Xiao Li pencil knife)Back to 16:12:08 score 30

The first one is incorrect. There is a problem.

Hehe writes a little bloated, without careful consideration, there must be a better solution top

Zjcxc (zhujian) on the 9th floor)Back to 16:16:02 score 50

-- If your product + unit + date can be used as the primary key, then:

Select item, Unit
, Purchase price 1 = max (Case Sid when 1 then purchase amount else 0 end)
, Price 2 = max (Case Sid when 2 then purchase amount else 0 end)
, Price 3 = max (Case sid When 3 then purchase amount else 0 end)
, Price 4 = max (Case Sid when 4 then purchase amount else 0 end)
, Price 5 = max (Case Sid when 5 then purchase amount else 0 end)
, Price 6 = max (Case Sid when 6 then purchase amount else 0 end)
, Price 7 = max (Case sid When 7 then purchase amount else 0 end)
From (
Select Commodity, unit, purchase amount, SID = (
Select count (*) from purchase table where item = A. item and unit = A. Unit and date of purchase <= A. date of purchase)
From incoming Table
) A group by product, unit top

Libin_ftsafe on the 10th floor (zomai: ts for banking card)Back to 16:18:11 score 20

Select Identity () as ID, A. * into # T from incoming table order by commodity ASC, unit ASC, date DESC


Select a. item,
A. Unit,
Sum (case when (A. id-b.id = 0) Then A. Amount else 0 end) as price 1,
Sum (case when (A. id-b.id = 1) Then A. Amount else 0 end) as price 2,
Sum (case when (A. id-b.id = 2) then A. Amount else 0 end) as price 3,
Sum (case when (A. id-b.id = 3) Then A. Amount else 0 end) as price 4,
Sum (case when (A. id-b.id = 4) Then A. Amount else 0 end) as price 5,
Sum (case when (A. id-b.id = 5) Then A. Amount else 0 end) as price 6,
Sum (case when (A. id-b.id = 6) Then A. Amount else 0 end) as price 7,
From
# T
Inner join
(Select Commodity, unit, min (ID) as ID from # T group by commodity, unit) B
On
A. Goods = B. Goods and A. unit = B. Unit
Group
A. Goods, A. Unit
Order
A. item, A. Unit top

Lsxaa (Xiao Li pencil knife) on the 11th floor)Back to 16:20:19 score 0

Good. The upstairs method is good. Top

Lsxaa (Xiao Li pencil knife) on the 12th floor)Back to 16:20:42 score 0

It's better to build a producer.

On the 13th floor, SS _ 328 (Hangzhou Branch)Back to 16:23:10 score 0

The first result is incorrect. More importantly, the speed is too slow. I used more than one minute to calculate the price of 1 (the data table is large and there are about 20000 data records)

Second
Execution time (the aggregate function cannot be executed for expressions that contain aggregation or subqueries .)

Lsxaa (Xiao Li pencil knife) Thank you
How can I filter the purchase table and keep only the last 7 purchase records (How many records are there less than 7 times)? I use the returned data to process it on the client, it can be compressed to 30 seconds. Top

On the 14th floor, SS _ 328 (zhuyun)Back to 16:29:49 score 0

Thanks for your help. The result of the pipeline creation method is as follows,
We can see that 0 exists in the middle, and the correct result should not contain 0 data. I have not fully understood your methods yet. How can I remove 0?


Product Code unit code
1 2 3 4
5
2017400000000100023000070000200005000002580.000000258000002580.00.00.0
2017400000000100023000292579.00.00.00.00.00.00.00.0
2017400000000100024000290.00.00.0000005097.05098.05098.0
2017400000000100025000294398.04396.00.00.00.00.00.0
20174000000001000260000700002000050.000000344900003449.00.0000003449.0
20174000000001000270000700002000052249.02249.02249.02249.02249.02249.02249.00.0
20174000000001000280000700002000054319000043190000431900004319.00.04349.00.0
2017400000000100028000294299.00.00.00.00.00.00.00.0
20174000000001000290000700002000052249.02249.02248.02249.02249.02249.02249.00.0
2017400000000100029000292249.02249.02250.000000225000002250.0
20174000000001000300000700002000053448.00.00.00.00.00.00.00.0
2017400000000100030000293448.0000003448.00000034490000374900003749.0
Top

Zjcxc (zhujian) on the 15th floor)Back to 16:31:08 score 0

-- The ID is used to write the primary key as follows:

Select item, Unit
, Purchase price 1 = max (Case Sid when 1 then purchase amount else 0 end)
, Price 2 = max (Case Sid when 2 then purchase amount else 0 end)
, Price 3 = max (Case sid When 3 then purchase amount else 0 end)
, Price 4 = max (Case Sid when 4 then purchase amount else 0 end)
, Price 5 = max (Case Sid when 5 then purchase amount else 0 end)
, Price 6 = max (Case Sid when 6 then purchase amount else 0 end)
, Price 7 = max (Case sid When 7 then purchase amount else 0 end)
From (
Select Commodity, unit, purchase amount, SID = (
Select count (*) from purchase table where item = A. item and unit = A. Unit and ID <= A. ID)
From incoming Table
) A where Sid <= 7 group by product, Unit
Top

Zjcxc (zhujian) on the 16 th Floor)Back to 16:32:06 score 0

Select item, Unit
, Purchase price 1 = max (Case Sid when 1 then purchase amount end)
, Price 2 = max (Case Sid when 2 then purchase amount end)
, Price 3 = max (Case sid When 3 then purchase amount end)
, Price 4 = max (Case Sid when 4 then purchase amount end)
, Price 5 = max (Case Sid when 5 then purchase amount end)
, Price 6 = max (Case Sid when 6 then purchase amount end)
, Price 7 = max (Case sid When 7 then purchase amount end)
From (
Select Commodity, unit, purchase amount, SID = (
Select count (*) from purchase table where item = A. item and unit = A. Unit and ID <= A. ID)
From incoming Table
) A where Sid <= 7 group by product, unit: Top

Lsxaa on the 17th floor (Xiao Li pencil knife)Back to 16:37:40 score 0

Filter.
Select *
From t
Where (select count (*)
From t
Where commodity = A. Commodity and unit = A. Unit
And date> = A. Date) <= 7
Top

Lsxaa on the 18 th floor (Xiao Li pencil knife)Back to 16:38:49 score 0

Have you tried libin_ftsafe? Top

19/F long0104 ()Back to 16:39:53 score 0

Select item, Unit
, Purchase price 1 = max (Case Sid when 1 then purchase amount else 0 end)
, Price 2 = max (Case Sid when 2 then purchase amount else 0 end)
, Price 3 = max (Case sid When 3 then purchase amount else 0 end)
, Price 4 = max (Case Sid when 4 then purchase amount else 0 end)
, Price 5 = max (Case Sid when 5 then purchase amount else 0 end)
, Price 6 = max (Case Sid when 6 then purchase amount else 0 end)
, Price 7 = max (Case sid When 7 then purchase amount else 0 end)
From (
Select Commodity, unit, purchase amount, SID = (
Select count (*) from purchase table where item = A. item and unit = A. Unit and date of purchase <= A. date of purchase)
From incoming Table
) A group by product, Unit
Top

On the 20th floor, SS _ 328 (Hangzhou)Back to 16:43:04 score 0

[Libin_ftsafe (zimo hongchen)?]

Trying,
Select Identity () as ID, A. * into # T from incoming table order by commodity ASC, unit ASC, date DESC

And select * From purchase table order by commodity ASC, unit ASC, date DESC

The sorting order is different. Why?

Top

2nd floor SS _ 328 (Hangzhou Branch)Back to 17:03:58 score 0

The zjcxc (zhujian) method has been tried successfully. Thank you very much.
Hash top

Gimy007 on the 22nd floor (catch a cat's mouse)Back to 17:11:56 score 0

Learning ~~~ Top

Related Questions

• A difficult query statistics. Please help me!
• For more difficult queries, please kindly advise
• It is difficult to find an SQL query statement.
• Difficult, multi-Table query
• It is difficult to query SQL statements !!
• Please help me with the difficult query questions!
• The query in access is difficult. Please help me
• (Urgent !) Difficult query statement
• Difficult query generation
• Difficult query Problems

Http://topic.csdn.net/t/20041224/15/3673923.html