重工电子论坛

标题: 这款眼镜相关SQL [打印本页]

作者: 李维强-15级 时间: 2016-7-18 01:07
标题: 这款眼镜相关SQL
下面这个SQL 是我重写眼镜权值排序的，红色部分是前端传来的数据 ID=120  就是文章的ID。我根据这个ID 去判断向后获取的列表有哪些

BEGIN
declare @now datetime,@_year int,@_month int,@_day int,@_NewsNum int;
set @now = GETDATE();
set @_year=DATEPART(YEAR,@now);
set @_month=DATEPART(MONTH,@now);
set @_day=DATEPART(DAY,@now);
SET @_NewsNum=(
SELECT rowNumber FROM (
select *,ROW_NUMBER() over (order by finalScale1 desc) rowNumber from (
select top 10000 * from
(
select finalScale1=
(
n1.Scale*0+
(
(DATEPART(YEAR,n1.ComeUpTime)-@_year)*365+
(DATEPART(MONTH,n1.ComeUpTime)-@_month)*30+
DATEPART(DAY,n1.ComeUpTime)-@_day
)*3+
n1.Clicked*0
),Id id,
Path path,
Title title,
CONVERT(nvarchar(30),CreatedAt,20) created_at,
HasVideo hasVideo
from dbo.News n1
left join dbo.NavAdTypeNews n2 on n1.Id =n2.News_Id AND  n1.Shown=1 AND n2.NavAdType_Id = 4
) as a order by a.finalScale1 desc
) as b ) as c WHERE id=120
);
SELECT * FROM (
select *,ROW_NUMBER() over (order by finalScale1 desc) rowNumber from (
select top 10000 * from
(
select finalScale1=
(
n1.Scale*0+
(
(DATEPART(YEAR,n1.ComeUpTime)-@_year)*365+
(DATEPART(MONTH,n1.ComeUpTime)-@_month)*30+
DATEPART(DAY,n1.ComeUpTime)-@_day
)*3+
n1.Clicked*0
),Id id,
Path path,
Title title,
CONVERT(nvarchar(30),CreatedAt,20) created_at,
HasVideo hasVideo
from dbo.News n1
left join dbo.NavAdTypeNews n2 on n1.Id =n2.News_Id AND  n1.Shown=1 AND n2.NavAdType_Id = 4
) as a order by a.finalScale1 desc
) as b ) as c WHERE rowNumber> @_NewsNum AND rowNumber<@_NewsNum+7
END

作者: 李维强-15级 时间: 2016-8-21 16:07

[HTML] syntaxhighlighter_viewsource syntaxhighlighter_copycode

<table width="100%">

    <tbody>

        <tr class="firstRow" height="auto">

            <td height="auto" width="10%">

                <p style="text-align:center">

                    <a href="http://v.cqutbbs.cn/Webapp/NewsLink/337" style="display: block; width: 100%; height: auto;"><img src="/uploadfile/image/20160821/6360739055743716252295300.gif" style="width: 90px; height: 90px;"/></a>

                </p>

            </td>

            <td style="vertical-align: middle;" width="60%">

                <div style="padding:10px;">

                    <a href="http://v.cqutbbs.cn/Webapp/NewsLink/337" title="" style=";font-family:宋体;font-weight:bold;font-size:21px;text-decoration:none;">深圳万新诚邀您参加2016北京展</a>

                    <p></p>

                </div>

            </td>

        </tr>

    </tbody>

</table>

欢迎光临重工电子论坛 (http://cqutlab.cn/)