How do i query this complex query in T-SQL / Linq?
Currently I have 2 table
[Category] -> PK| CAT_ID -> CAT_PARENT (link to itself | if it's a top parent category then it's 0)
[Posts] -> PK | POST_ID -> FK | CAT_ID | CREATE_DATE
How do I select top 15 ROWS of Posts in every CAT_PARENT which have multiple child category. So the total Posts in CAT_PARENT and all it's child are only 15. we have multiple CAT_PARENT here. so we can return multiple group of post w开发者_开发技巧hich contain 15 post for every CAT_PARENT and it's child category
The problem here is to have it in one round trip of query to the SQL server since one query can take up to 200 posts so it could be the best if you can write it in 1 T-sQL query / linq query.
I prefer if you can write it in LINQ. But it's also OK to write it in T-SQL.
Thank you very much :)
Thanks to Alexander's solution down here, i modified some part and it work well with 186 query and some lazy loaded column for less than 2s (remotely) to my SQL server
ALTER procedure [dbo].[get_topParentPost] (
@quantity int
)
as
WITH t AS (
SELECT ROW_NUMBER() OVER (PARTITION BY top_level.CAT_ID ORDER BY p.CREATE_DATE DESC) AS row_num,
top_level.CAT_ID AS top_level_cat_id, child_category.CAT_ID AS category_id, p.POST_ID, p.CREATE_DATE, p.VALIDATE,
p.CAT_ID, p.DESCRIPTION, p.DRAF_OF, p.END_DATE, p.MOD_DATE, p.ON_HOMEPAGE, p.PUBLISH_DATE, p.[STATE], p.THUMB_ID, p.TITLE, p.[TYPE],
p.[VIEW]
FROM
(SELECT cat_id, 0 as cat_parent FROM Categories c WHERE CAT_PARRENT = 0) AS top_level
INNER JOIN Categories AS child_category
ON child_category.CAT_PARRENT = top_level.CAT_ID OR child_category.CAT_ID = top_level.CAT_ID
INNER JOIN Posts p
ON child_category.CAT_ID = p.CAT_ID AND p.VALIDATE = 1
)
SELECT * FROM t WHERE row_num <= @quantity
I modified some part which helps the query select top 15 according to descending date instead of ID ascending
If it's for SQL Server 2005 or later, you can do:
SELECT t.top_level_cat_id, t.category_id, t.post_id, t.post_date
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY top_level.cat_id ORDER BY p.post_id) AS row_num,
top_level.cat_id AS top_level_cat_id,
child_category.cat_id AS category_id,
p.post_id,
p.post_date
FROM
Post p
INNER JOIN Category AS child_category
ON child_category.cat_id = p.cat_id
INNER JOIN Category AS top_level
ON top_level.cat_id = child_category.cat_parent
) AS t
WHERE t.row_num <= 15
Here's T-SQL I used for creating test tables:
CREATE TABLE Category (cat_id INT, cat_parent INT);
--top level
INSERT INTO Category VALUES(1, 0);
INSERT INTO Category VALUES(2, 0);
-- child categories
INSERT INTO Category VALUES(3, 1);
INSERT INTO Category VALUES(4, 1);
INSERT INTO Category VALUES(5, 2);
CREATE TABLE Post(post_id INT, cat_id INT, post_date DATETIME);
INSERT INTO Post VALUES(1, 3, GETDATE());
INSERT INTO Post VALUES(2, 3, GETDATE());
INSERT INTO Post VALUES(3, 3, GETDATE());
INSERT INTO Post VALUES(4, 3, GETDATE());
INSERT INTO Post VALUES(5, 3, GETDATE());
INSERT INTO Post VALUES(6, 3, GETDATE());
INSERT INTO Post VALUES(7, 3, GETDATE());
INSERT INTO Post VALUES(8, 3, GETDATE());
INSERT INTO Post VALUES(9, 3, GETDATE());
INSERT INTO Post VALUES(10, 3, GETDATE());
INSERT INTO Post VALUES(11, 3, GETDATE());
INSERT INTO Post VALUES(12, 3, GETDATE());
INSERT INTO Post VALUES(13, 3, GETDATE());
INSERT INTO Post VALUES(14, 3, GETDATE());
INSERT INTO Post VALUES(15, 3, GETDATE());
-- these records won't appear
INSERT INTO Post VALUES(16, 3, GETDATE());
INSERT INTO Post VALUES(17, 4, GETDATE());
INSERT INTO Post VALUES(18, 4, GETDATE());
INSERT INTO Post VALUES(19, 5, GETDATE());
INSERT INTO Post VALUES(20, 5, GETDATE());
I'm not sure if there's any way to do this with linq without pulling more information than is necessary, but here is a T-SQL script that will accomplish the task. Just to make sure I understood the requirement, here is my description of the script:
This script selects the top 15 entries from the Posts table whose Category has more than 1 sub-category:
declare @cat_id int
declare MULTIPLE_CHILDREN_CATEGORIES cursor for
(
select cat_id
from categories
group by cat_parent
having count(*) > 1
)
open MULTIPLE_CHILDREN_CATEGORIES
fetch next from MULTIPLE_CHILDREN_CATEGORIES into @cat_id
while @@fetch_status = 0
begin
select top 15 *
from posts
where cat_id = @cat_id
order by create_date desc
fetch next from MULTIPLE_CHILDREN_CATEGORIES into @cat_id
end
close MULTIPLE_CHILDREN_CATEGORIES
deallocate MULTIPLE_CHILDREN_CATEGORIES
I imagine something like this for linq2sql:
IEnumerable<IEnumerable<Post>> posts = db.Categorys
.Where(p=>p.CAT_PARENT == 0)
.Select(p=>p.Categorys
.SelectMany(q=>q.Posts)
.OrderByDescending(q=>q.CREATE_DATE)
.Take(15))
精彩评论