////3月24日
SELECT top 50 [Id]
,[ColumnName]
,[Title]
,[AddDate]
,[ViewNum]
,[Author]
,[TColor]
,[KeyWord]
,[Summary]
FROM [xkCms].[dbo].[Xk_ArticleList]
where 1=1 and ispass=1 and subtitle ='' and DATEDIFF(day,getdate(),AddDate)>-8
order by viewnum desc,id desc
<div class="click_bottom_left_topc">
{$_begin_$}{$_gettxt(50, and ispass=1 and subtitle ='' and DATEDIFF(day,getdate(),AddDate)>-8 order by viewnum desc,id
desc,false )_$}
<ul>
<li id="h_font"><a href="{$_url_$}" target="_blank">{$_title_$}</a></li>
<li id="h_fontc">{$_date_$}</li>
</ul>
{$_end_$}
</div>
////在程序中，SQL代码换行需用"+"来连接
SELECT top 50 '0' ordernum,[Id],[Title],[AddDate],[Author] " +
",(select title from xk_source where id=Xk_ArticleList.sourceid) sourcename "+
"FROM [xkCms].[dbo].[Xk_ArticleList] "+
"where ispass=1 and subtitle ='' and DATEDIFF(day,getdate(),AddDate)>-8 "+
"order by viewnum desc,id desc"
/////
//SELECT top 50
'0' ordernum //新增加一个字段，并赋值0；
,[Id]
,[Title]
,[AddDate]
,[Author]
,(select title from xk_source where id=Xk_ArticleList.sourceid) sourcename // 嵌套搜索，把搜索结果作为列 sourcename 加在表
[Xk_ArticleList] 最后，形成sourcename的字段；
FROM [xkCms].[dbo].[Xk_ArticleList]
where ispass=1 and subtitle ='' and DATEDIFF(day,getdate(),AddDate)>-8 //最近一周内的信息；
order by viewnum desc,id desc
03.26
//文章 上一节 下一节 sql 存储过程代码
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[ProChangePage]
(
@id int,
@preid int out,
@nextid int out
)
as
begin
set nocount on
declare @temp_orderid int--指定@id的排序值
create table #temp_book
(
tempid int IDENTITY(1,1) NOT NULL ,
bookid int NULL
)
--依次导入所有符合条件的文章id
insert into #temp_book (bookid)
select id from [Xk_ArticleList] where columnid=90
and ispass =1 and title like '第一章%'
insert into #temp_book (bookid)
select id from [Xk_ArticleList] where columnid=90
and ispass =1 and title like '第二章%'
insert into #temp_book (bookid)
select id from [Xk_ArticleList] where columnid=90
and ispass =1 and title like '第三章%'
insert into #temp_book (bookid)
select id from [Xk_ArticleList] where columnid=90
and ispass =1 and title like '第四章%'
insert into #temp_book (bookid)
select id from [Xk_ArticleList] where columnid=90
and ispass =1 and title like '第五章%'
insert into #temp_book (bookid)
select id from [Xk_ArticleList] where columnid=90
and ispass =1 and title like '第六章%'
insert into #temp_book (bookid)
select id from [Xk_ArticleList] where columnid=90
and ispass =1 and title like '第七章%'
insert into #temp_book (bookid)
select id from [Xk_ArticleList] where columnid=90
and ispass =1 and title like '第八章%'
--
--取出@id的排序值
select @temp_orderid =tempid from #temp_book where bookid=@id
--把上一条id值赋给@preid
select @preid=max(tempid) from #temp_book where tempid<@temp_orderid
if @preid is null
set @preid = @id
else
select @preid=bookid from #temp_book where tempid=@preid
--把下一条id值赋给@nextid
select @nextid=min(tempid) from #temp_book where tempid>@temp_orderid
if @nextid is null
set @nextid = @id
else
select @nextid=bookid from #temp_book where tempid=@nextid
drop table #temp_book
set nocount off
end
//sql if 语句判断 示例
USE pubs
IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') < $15
BEGIN
PRINT 'The following titles are excellent mod_cook books:'
PRINT ' '
SELECT SUBSTRING(title, 1, 35) AS Title
FROM titles
WHERE type = 'mod_cook'
END
ELSE
PRINT 'Average title price is more than $15.'
////
查看文章
一段运用sql语句创建临时表的使用2007-05-30 11:46首先我们来熟悉下临时表的概念:
临时表与永久表相似，但临时表存储在 tempdb 中，当不再使用时会自动删除。
临时表有两种类型：本地和全局。它们在名称、可见性以及可用性上有区别。本地临时表的名称以单个数字符号 (#) 打头；它们仅对当前的用
户连接是可见的；当用户从 SQL Server 实例断开连接时被删除。全局临时表的名称以两个数字符号 (##) 打头，创建后对任何用户都是可见
的，当所有引用该表的用户从 SQL Server 断开连接时被删除。
例如，如果创建了 employees 表，则任何在数据库中有使用该表的安全权限的用户都可以使用该表，除非已将其删除。如果数据库会话创建了
本地临时表 #employees，则仅会话可以使用该表，会话断开连接后就将该表删除。如果创建了 ##employees 全局临时表，则数据库中的任何
用户均可使用该表。如果该表在您创建后没有其他用户使用，则当您断开连接时该表删除。如果您创建该表后另一个用户在使用该表，则 SQL
Server 将在您断开连接并且所有其他会话不再使用该表时将其删除。
conn.Execute("SELECT newscomment_user_id AS user_id, COUNT(*) AS counter INTO #userNewsComment1 " _
& " FROM newsComment GROUP BY newscomment_user_id ")
'把从newsComment中或的数据集合写入到临时表 #userNewsComment1 中
'newsComment是新闻评价表
Set rs = conn.Execute("SELECT TOP 10 a.user_id AS trueId, * FROM userInfo AS a INNER JOIN userPoint AS b " _
& " ON a.user_id = b.user_id INNER JOIN View_Company AS c ON a.user_id = c.user_id " _
& " LEFT OUTER JOIN #userNewsComment1 AS d ON d.user_id = a.user_id " _
& " WHERE user_isProvinceNewsShow = 1 " _
& placeCondition & " ORDER BY d.counter DESC ")
conn.execute("drop table #userNewsComment1")
'使用完记住删除临时表，这里是删除临时表对象
这里是把临时表运用上
////把表[xkCms].[dbo].[北京媒体$] 中的数据插入到 [xkCms].[dbo].[hfc_crm]
INSERT INTO [xkCms].[dbo].[hfc_crm]
(category ,telephone1 ,telephone2 ,companyname ,username ,email )
select 类别,电话,移动电话,单位,姓名,电子邮件 from [xkCms].[dbo].[北京媒体$]
////又一种插入形式(使用中)
INSERT INTO [xkCms].[dbo].[hfc_crm]
(category ,telephone1 ,telephone2 ,companyname ,username ,email ,author )
select 类别, 移动电话, 电话,单位,姓名,电子邮件,'谢红玲' //'谢红玲'不是搜索所得
from [xkCms].[dbo].[北京媒体$]
////删除表的内容方法
truncate table [hfc_crm]
或者，drop table [hfc_crm] （直接用drop速度慢，先 truncate 后drop 速度快一些）
///
////////
--把[xkCms].[dbo].[Sheet1-3-7] 中的数据插入到 [xkCms].[dbo].[hfc_crm]
INSERT INTO [xkCms].[dbo].[hfc_crm]
(username,category ,job,telephone1 ,email,companyname,author )
select 分类, 分类, 职务,手机,Email, 所在公司名称,录入人
from [xkCms].[dbo].[Sheet1-3-7]
//////3.28
RecommendPiclist 连接数据库代码 其中的一个保护类；
protected void getListBox()
{
//e=0表示管理首页，列出已推荐和未推荐的图片新闻；e=1表示已推荐的图片新闻;e=2表示未推荐的图片新闻
int state = q("e") == "" ? 1 : Convert.ToInt32(q("e"));
int page = Convert.ToInt32("0" + q("page"));
int rowcout = 0;
//string sqlstr = "select * from xk_articlelist where img<>'' and img is not null and subtitle=''
and ispass=1 order by id desc ";
DataTable dt = GetDataTable("xk_articlelist" +
"[Id]" +
",[ChannelId]" +
",(select top 1 dir from Xk_Channel where Xk_Channel.id=[Xk_ArticleList]. [ChannelId]) dir" + //把
搜索出来的结果作为列 ：dir;
",[ColumnId]" +
",[orderNum]" +
",[IsPass]" +
",[ColumnName]" +
",[Title]" +
",[ViewNum]" +
",[ReviewNum]" +
",(select master_name from xk_master where master_id=[Xk_ArticleList].userid) username" + //把搜
索出来的结果作为列 ：username;
",[IsTop]" +
" ,[OutUrl] "+
" id ", true //以id排序;
+ " id "
+ page, 20, //当前页，每页size：20
+ref rowcout,
" img<>'' and img is not null and subtitle='' and ispass=1 "//最终SQL搜索条件
);
ArticleList.DataSource = dt;//确定数据源；
ArticleList.DataBind();//与数据源做绑定；
}
////////////// 4.1
/////////////把媒体表的数据导入[hfc_crm]
INSERT INTO [xkCms].[dbo].[hfc_crm]
(category ,province,telephone1 ,telephone2 ,companyname ,username ,email ,author )
select 广东, '广东',移动电话, 电话,单位,姓名,电子邮件,'谢红玲'
from [xkCms].[dbo].[广东媒体$]
/////update [xkCms].[dbo].[hfc_crm]
set telephone1='86013901191933'
where id=638
///update [xkCms].[dbo].[hfc_crm]
set province='北京'
where author = '石静'
////
INSERT INTO [xkCms].[dbo].[hfc_crm]
(username,category ,job,telephone1 ,email,companyname,author,province)
select 分类, 分类, 职务,手机,Email, 所在公司名称,'石静' ,'北京’
from [xkCms].[dbo].[shijing$]
////4.3 -----表字段类型转换
update [xkCms].[dbo].[hfc_crm]
set tel = CAST(CAST(telephone1 AS DECIMAL(25,0)) AS VARCHAR(30))
-----------更改列名，
exec sp_rename 'hfc_crm.[telephone1]','telfloat','column'
hfc_crm.[telephone1] 表hfc_crm中的telephone1字段改为telfloat
------------巧妙取出一个大数据量表中符合条件的20条数据，--分页时用
select top 20 [Id],[ChannelId],
(select top 1 dir from Xk_Channel
where Xk_Channel.id=[Xk_ArticleList].[ChannelId]) dir,[ColumnId],[orderNum],[IsPass],[ColumnName],[Title],[ViewNum],
[ReviewNum],(select master_name from xk_master where master_id=[Xk_ArticleList].userid) username,[IsTop] ,[OutUrl]
from xk_articlelist
where img<>'' and img is not null and subtitle='' and ispass=1 and istop=0
and id < (select MIN( id ) from (select top 60 id from xk_articlelist where img<>'' and img is not null and subtitle=''
and ispass=1 and istop=0 order by id desc) as t) order by id desc
----------------------------
INSERT INTO [housechina].[dbo].[HouseInfo]
([isPass] ,[houseName] ,[houseCode])
VALUES
('1' ,'多多' ,'23423' )