怎样创建用户级别表？

[征服者X]

用户注册后有级别显示:☆（1-30天）、☆☆（30-120天）、☆☆☆（120-360天）、☆☆☆☆（360-1000天）、☆☆☆☆☆（1000天以上

[you_tube]

在数据库里记录用户注册时间，然后查询处理当然级别显示，这样？

[Grave_rain]

select class=
case
when day between 0 and 30 then '*'
when day between 31 and 120 then '**'
when day between 121 and 359 then '***'
when day between 360 and 1000 then '****'
else '*****'
end
from 表名

[dawugui]

[Quote=引用楼主 li_ser 的帖子:]
用户注册后有级别显示:☆（1-30天）、☆☆（30-120天）、☆☆☆（120-360天）、☆☆☆☆（360-1000天）、☆☆☆☆☆（1000天以上
[/Quote]

--1
假设注册时间为DT.

select case when dt between 1 and 29 then '☆'
            when dt between 30 and 119 then '☆☆'
            when dt between 120 and 359 then '☆☆☆'
            when dt between 360 and 999 then '☆☆☆☆'
            when dt >= 1000 then '☆☆☆☆☆'
       end
from tb
 

--2
建立三个字段
col1 col2 col3
1    29   '☆'
30   119  '☆☆' 
120  359  '☆☆☆'
360  999  '☆☆☆☆'
1000 99999'☆☆☆☆☆'

select col3 from tb where datediff(day , dt , getdate()) between col1 and col2

[dawugui]

--1,这种写法简单,不过不灵活.
假设注册时间为DT.

select case when dt between 1 and 29 then '☆'
            when dt between 30 and 119 then '☆☆'
            when dt between 120 and 359 then '☆☆☆'
            when dt between 360 and 999 then '☆☆☆☆'
            when dt >= 1000 then '☆☆☆☆☆'
       end
from tb
 

--2,这种看似复杂,不过灵活一些,当☆改为其他的符号时一样可以用.
建立三个字段(tb2)
col1 col2 col3
1    29   '☆'
30   119  '☆☆' 
120  359  '☆☆☆'
360  999  '☆☆☆☆'
1000 99999'☆☆☆☆☆'
数据表tb1,其中含有注册时间字段

select col3 from tb1 , tb2 where datediff(day , tb1.dt , getdate()) between tb2.col1 and tb2.col2

[htl258_Tony]

除了2楼直接查询的方法外,可以做个固定表,定时更新,具体得根据数据量的大小与实际的需求而定.

[wangjianming45]

CREATE TABLE dbo.UserReg
(
ID INT IDENTITY(1,1) NOT NULL
,UserID INT NOT NULL
,RegDate DATETIME NULL
CONSTRAINT Df_UserReg_RegDate DEFAULT(GETDATE())
,CONSTRAINT PK_UserReg PRIMARY KEY
(
ID ASC
)
)
--truncate table dbo.UserReg
INSERT INTO dbo.UserReg(UserID,RegDate)
SELECT 200,GETDATE()-30 UNION ALL
SELECT 300,GETDATE()-61 UNION ALL
SELECT 400,GETDATE()-121 UNION ALL
SELECT 500,GETDATE()+1 UNION ALL
SELECT 600,GETDATE()-1001

SELECT UserID
,CASE WHEN DATEDIFF(d,RegDate,GETDATE()) BETWEEN 1 AND 30 THEN N'☆'
WHEN DATEDIFF(d,RegDate,GETDATE()) BETWEEN 31 AND 120 THEN N'☆☆'
WHEN DATEDIFF(d,RegDate,GETDATE()) BETWEEN 121 AND 360 THEN N'☆☆☆'
WHEN DATEDIFF(d,RegDate,GETDATE()) BETWEEN 361 AND 1000 THEN N'☆☆☆☆'
WHEN DATEDIFF(d,RegDate,GETDATE())>1000 THEN N'☆☆☆☆☆'
ELSE ''
END
FROM dbo.UserReg WITH (NOLOCK)

[ws_hgo]

create Trigger TR_Type on tablename
after update --当用户登陆的时候更新其登陆的天数
as 
  upadte tablename
  set [Type]=case when dt between 1 and 29 then '☆'
            when dt between 30 and 119 then '☆☆'
            when dt between 120 and 359 then '☆☆☆'
            when dt between 360 and 999 then '☆☆☆☆'
            when dt >= 1000 then '☆☆☆☆☆'
       end
from tb

begin

[orochi_gao]

需要有这三个栏位:uid,createdate,level
每天做任务定时更新level

[yeah86]

学习来了！！

[readfuture]

create Trigger TR_Type on tablename
after update --当用户登陆的时候更新其登陆的天数
as
upadte tablename
set [Type]=case when dt between 1 and 29 then '☆'
when dt between 30 and 119 then '☆☆'
when dt between 120 and 359 then '☆☆☆'
when dt between 360 and 999 then '☆☆☆☆'
when dt >= 1000 then '☆☆☆☆☆'
end
from tb

begin

[ljluck7687]

表中应有三个字段：
UId UName UDate
分别是用户编号、用户姓名、注册日期。如果涉及到其他功能还可以增加其他字段

显示时用：
select UType=
case
when day between 0 and 30 then '☆（1-30天）'
when day between 31 and 120 then '☆☆（30-120天）'
when day between 121 and 359 then '☆☆☆（120-360天）'
when day between 360 and 1000 then '☆☆☆☆（360-1000天）'
else '☆☆☆☆☆（1000天以上)'
end
from Users

[Andy__Huang]

help up!

[winter75]

[Quote=引用楼主 li_ser 的帖子:]
用户注册后有级别显示:☆（1-30天）、☆☆（30-120天）、☆☆☆（120-360天）、☆☆☆☆（360-1000天）、☆☆☆☆☆（1000天以上
[/Quote]

SELECT UserID 
,CASE WHEN DATEDIFF(d,RegDate,GETDATE()) BETWEEN 1 AND 30 THEN N'☆' 
WHEN DATEDIFF(d,RegDate,GETDATE()) BETWEEN 31 AND 120 THEN N'☆☆' 
WHEN DATEDIFF(d,RegDate,GETDATE()) BETWEEN 121 AND 360 THEN N'☆☆☆' 
WHEN DATEDIFF(d,RegDate,GETDATE()) BETWEEN 361 AND 1000 THEN N'☆☆☆☆' 
WHEN DATEDIFF(d,RegDate,GETDATE())>1000 THEN N'☆☆☆☆☆' 
END 
FROM tablename