关于处理论坛,信息,用户控制面板站内短信。
数据为站内段信相关信息。
未处理数据
处理完的数据
CREATE TABLE :
USE [my]
GO
/****** 对象: Table [dbo].[Site_SMS] 脚本日期: 10/28/2007 00:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Site_SMS](
[SMS_Id] [int] IDENTITY(1,1) NOT NULL,
[SMS_Info] [nvarchar](500) NULL,
[SMS_Title] [nvarchar](50) NOT NULL,
[SMS_PutTime] [datetime] NULL,
[SMS_SendUserId] [nvarchar](50) NOT NULL,
[SMS_ReceiveUserId] [nvarchar](50) NOT NULL,
[SMS_ISHidden] [bit] NOT NULL CONSTRAINT [DF_Site_SMS_SMS_ISHidden] DEFAULT ((0)),
[SMS_ISSystem] [bit] NOT NULL CONSTRAINT [DF_Site_SMS_SMS_ISSystem] DEFAULT ((0))
) ON [PRIMARY]
expression:
SELECT SMS_Id, SMS_Info, SMS_Title, SMS_PutTime, SMS_SendUserId , SMS_ReceiveUserId, SMS_ISHidden, SMS_ISSystem
FROM Site_SMS where SMS_ReceiveUserId='123' OR (SMS_ReceiveUserId='Every' and SMS_ISSystem='true')
order by SMS_ISSystem DESC,SMS_PutTime desc ,SMS_Id
data:
12 123 123 2207-12-19 00:00:00.000 admin Every 1 1
7 123 123 2207-12-14 00:00:00.000 admin Every 1 1
3 123 123 2207-12-10 00:00:00.000 admin Every 1 1
9 123 123 2207-12-16 00:00:00.000 234 123 0 0
6 123 123 2207-12-13 00:00:00.000 123 123 0 0
5 123 123 2207-12-12 00:00:00.000 456 123 0 0
4 123 123 2207-12-11 00:00:00.000 123 123 0 0
2 123 123 2207-12-09 00:00:00.000 123 123 0 0
查询条件为 条件1:SMS_ReceiveUserId 接收者为‘123’,条件2:接收者为‘Every’并且SMS_ISSystem=true 是系统信息的。
然后排序 首先按排序1: SMS_ISSystem DESC 系统信息被排在最前面。
排序2:SMS_PutTime desc 再按时间倒序排列。