SQL Server delete??????

2014?12?26? by Jorkin

?????????????????
DELETE t1 FROM [?] t1 WHERE EXISTS (..);

????

2014?12?12? by Jorkin

“????”??????????”???·???Edward A. Murphy?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????

??????????????????

1???????????????????????????
2?????????????????????????
3????????????????????????????????
4??????????????
5???????????????????????????
6???????????????????????——????????????
7?????——????????????????????????
8??????????????????????????????
9??????????????????????
10?????????????????????????????
11????????????????????????????????????
12???????????
13???????60????????????61????????
14?????????????????????????????????
15?????????????????????????????????
16?????????????????
17??????????????????????????
18??????????????????????????????????????????????????????
19???????? ?????????????????????
20?????????
21?????????
22???????????????????????
23??????????
24????????????
25??????????????????????
26?????????????????????????
27?????????
28???????????????
29????????????
30?????????
31????????????

?????????????????

2014?12?11? by Jorkin

http://www.zdic.net/z/15/zy/4E70.htm

http://www.zdic.net/z/a4/js/27D7D.htm

CDM?LDM?PDM???

2014?11?10? by Jorkin

???????????????????????????????????????????????

?????????????conceptual data model????????“??????”?

??????????????????????????????????????????????????????????????????????????????????

??????????????????????????????????????????????????????????????????????????

?????????????????????????????????????????????????????

??????????????????????????????????????

?????????????logical data model????????“??????”?

????????????????????????????????????????????????????????????????????????????????????????????

??????????????????????????????????????????????????????????

??????????????????????????????????????

??????????????????????????????????????????????????????????????????????????????????

?????????????physical data model????????“??????”?

????????????????????????????????????????????????????????????

?????????????????????????????????????????????????????????????????????????????????????????

????????????????????????????????????????

????????????

2014?10?20? by Jorkin

??????????SQL?????????????????????????????????????????????????????????????????????????????????????????????

??????????????????
```
SELECT * FROM [?] WHERE ID IN (?);
```
????????????
```
SELECT * FROM ? ORDER BY ?;
```
SQL??????????
```
SELECT * FROM [?] ORDER BY ID ?;
```

Excel????CSV???????????????????

2014?7?30? by Jorkin

???csv???,????????”=”???excel???????????
? =”9876543210″,=“1234567890”,

???????????????

2014?4?1? by Jorkin

1.???????????????????????????????????????????????????…?????????????????????????????????????”

2.?????????????”?????????????????????”?????”???”

3.???????2?????1??10?????????0?1????????????”??????????????””0?1?2?3?”?????????????”???0??”?????”????????????”

4.??????????????????????????????????????????? ??????????????????????????????????????????? ???????????????????hello world?

5.?????????????????????????????bug?

6.?????3?????????Ctrl?Alt ?Delete??????????????????????????…

7.?????????????1.0???

8.??????????????????”?TM?????????????”

9.??????????bug????

10.????????????????????

11.??????????????debug???????????????????bug???

12.??????????????????????????????????

13.bug????????????????404???????????????????????????

14.?xx????????????????????????????????????????????????xx?testing???????????????”???????????…”???????xx?????????????????

15.??????

A???????????????
B????????
A??????
B?????????????????????????
16.????????????????????????????????????????????????????”F1!””F1!”??????”F1″????????

17.

1???????????……????????????????~?????????????????~
2?????????????????????????????????
3??????????????????? ???????????????????
4???????????????????? ?????????????????
5?????????????????????? ????????????,???????
18.?????????????.

??: ???,??????????.
???: ????????????????.
??: ???!??????,???????!
???: ??!??????????????6?????.
??: ????????????.
19.???????????????????????????

20.???????????????????????????????????

21.???????????????? ?????Bug???? ???????????????? ???????????????? ??????????? ???????????????,???.

22.????????????if???else???????????????????

23.?????????????????????????????????????????????????

24.?????????????????????????????????’?’?’??’????

???”OK!”
?????????????????????????????????????????????????????????
?????????
25.??????????MyEclipse????????????????????????????????????????????????????????????????? ????????????????????????

26.????????????????????”?????????????????????????

27.????????????????????????????????????????

28.?????ing???????????????????????????????????????????????????????????????????????????????????

29.????????????warning????????error?

30.??????IT??????????????????????????????????????????????????????????????????IT???????????????????????TM???????????????????????

31.?????bug??????????????bug?????10????????????10???”??????????????????”???”????????????????????????

32.???????????????????????

33.??????????50??????????????????????50??????BUG?????????????????50????????????????????????50????????????????

34.????????????????????????????~

35.??????????????????????????????????????????????????????????????????

36.???????????????????????????????X???????”hello world?”

37.????????????????????????????????????????????bug……

38.????????????????????????????????????MSN??????????????????????????????????????????????????????????????????

39.???????????????????????????????????????????????????????????????????????????????????

40.???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????bug???????????????bug????????????????

41.????????IT???”?????????” ??, ?????: ??????????????? ?: ????????, ??????????.

42.??????????????????????????JQuery, PHP ???????????????Hello World??????????”??????????????????????????????????????????”$”????????????

43.???????????”?????????????????????????”?????”?????????????????????”?????????”??????????????????

44.?????????????????????????????…???????????????????????????????????????????????????????…?????????…?rz

45.????????????? ?? “????” ???????????????? “????”?????????????

46.???????it?????????????AA???????????????????????????????????????????????????????????

47.?????????????????????????????????????????????

48.??????????????????CPU?????????????????????????????????

49.??????????. ???Oracle???; ???win7???; ????IE?????; ???Gmail?????, ??????????? ? ???????????? ????????????? ???????????????

50.???????????????????????????????????????????????????????????????????

51.???????????????????????????????? …

52.????????????????????????100????????????????????????????????????……?????????????????????????????????——?????????????0????.

53.???????????????????????????

???int????????
??4??
?????????
???????
????????????
????????????????????????????……

SQL Server——????fn_Split??

2014?3?6? by Jorkin

??SQL2000??ntext?bug?

CREATE FUNCTION [dbo].[Fn_split] (@expression NTEXT,
                                  @delimiter  NVARCHAR(2) = ',')
RETURNS @expressionstable TABLE (
  [DUMMY] NVARCHAR(4000))
AS
  BEGIN
      DECLARE @currentindex INT
      DECLARE @nextindex INT
      DECLARE @returntext NVARCHAR(4000)
      DECLARE @datalength INT
      IF @expression IS NULL
        BEGIN
            INSERT INTO @expressionstable
                        ([DUMMY])
            VALUES      (NULL)
        END
      ELSE
        BEGIN
            SELECT @datalength = Datalength(@expression) / 2
            IF @datalength = 0
              INSERT INTO @expressionstable
                          ([DUMMY])
              VALUES      ('')
            ELSE
              BEGIN
                  SELECT @currentindex = 1
                  WHILE( @currentindex <= @datalength )
                    BEGIN
                        SELECT @nextindex = Charindex(@delimiter, Substring(@expression, @currentindex, 4000))
                                            + @currentindex
                        /*????Charindex????????8000??????????????*/
                        IF( @nextindex = @currentindex )
                          SELECT @nextindex = @currentindex + @datalength
                        SELECT @returntext = Substring(@expression, @currentindex - 1, @nextindex - @currentindex)
                        INSERT INTO @expressionstable
                                    ([DUMMY])
                        VALUES      (@returntext)
                        SELECT @currentindex = @nextindex + 1
                    END
              END
        END
      RETURN
  END

CREATE FUNCTION [dbo].[Fn_split] (@str       VARCHAR(MAX),
                                  @separator VARCHAR(10))
RETURNS TABLE
AS
    RETURN
      (SELECT T0.DUMMY
       FROM   ( (SELECT [DUMMY] = CONVERT(XML, '<DUMMY>'
                                             + Replace(@str, @separator, '</DUMMY><DUMMY>')
                                             + '</DUMMY>')) T1
                OUTER APPLY (SELECT DUMMY = N.v.value('.', 'NVARCHAR(4000)')
                             FROM   T1.[DUMMY].nodes('/DUMMY') N(v)) T0 ))

???

SELECT * FROM fn_split('1,2,3,4,5', ',');

??Transact-SQL????

2014?2?27? by Jorkin

Transact-SQL????????

RAND:?????? 0 ? 1???? 0 ? 1??????? float ??????? seed?? SQL Server ????????????? ???????????? RAND() ???????????????????????????? RAND()?? RAND() ??????????????????? RAND() ???????

NEWID:?? uniqueidentifier ???????NEWID ???????????????

CHECKSUM:??????????????????????????????????CHECKSUM ??????????????????????????????? CHECKSUM ??(???????????????)

??RAND()???????????? RAND() ????????????????????SELECT???????????????????????????????????????

?????CHECKSUM(NEWID())??RAND(CHECKSUM(NEWID()))????

?????????????

[????]??????SQL????????????-??????

2014?2?7? by Jorkin

????????SQL?????????????????????????????

???????????????

CREATE TABLE [dbo].[E_Classes] ([ClassID] varchar(12) NOT NULL ,[BeginDate] date NULL ,[EndDate] date NULL ,[StudentCount] smallint NULL);
INSERT INTO [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'01', N'2011-10-07', N'2012-03-11', N'49');
INSERT INTO [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'02', N'2012-04-05', N'2012-06-20', N'23');
INSERT INTO [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'03', N'2012-05-01', N'2012-07-03', N'46');
INSERT INTO [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'04', N'2013-03-08', N'2013-06-12', N'42');
INSERT INTO [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'05', N'2013-09-19', N'2013-10-26', N'78');
INSERT INTO [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'06', N'2013-11-21', N'2014-01-09', N'44');
INSERT INTO [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'07', N'2013-12-27', N'2014-02-06', N'36');
INSERT INTO [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'08', N'2014-02-28', N'2014-06-20', N'52');

???????????????????????????

class_total

????? ????????????*????????2014?01????? ??06? 2014-01-01~2014-01-09??9?*44?=396?? ??? ??07?2014-01-01~2014-01-31??31?*36?=1116?????2014?01????? 396+1116=1512????

?????????????????????????????????:

CREATE TABLE [dbo].[E_Sequence]([Sequence_ID] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_E_Sequence] PRIMARY KEY CLUSTERED ([Sequence_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]);
SET IDENTITY_INSERT [dbo].[E_Sequence] ON
INSERT INTO [dbo].[E_Sequence] (Sequence_ID)
SELECT number FROM [master]..[spt_values] WHERE  type = 'P'
UNION ALL
SELECT number + 2048 FROM [master]..[spt_values] WHERE  type = 'P'
SET IDENTITY_INSERT [dbo].[E_Sequence] OFF

??????????????????????????

SELECT Dateadd(day, Sequence_id, '2011-1-1') AS Class_Date FROM E_Sequence

????????????????2011?01?01????4000?????????????????????????????

SELECT Year(Class_Date)  AS Class_Year,
       Month(Class_Date) AS Class_Month,
       StudentCount
FROM   (SELECT Dateadd(day, Sequence_id, '2011-1-1') AS Class_Date
        FROM   E_Sequence) t1,
       E_Classes t2
WHERE  t1.Class_Date BETWEEN t2.BeginDate AND t2.EndDate

?????

OK??????????????“?”?????“?”?????“???”????????????????????????????SQL2000?????Case When End??

SELECT Class_Year      AS ??,
       Isnull([1], 0)  AS ??,
       Isnull([2], 0)  AS ??,
       Isnull([3], 0)  AS ??,
       Isnull([4], 0)  AS ??,
       Isnull([5], 0)  AS ??,
       Isnull([6], 0)  AS ??,
       Isnull([7], 0)  AS ??,
       Isnull([8], 0)  AS ??,
       Isnull([9], 0)  AS ??,
       Isnull([10], 0) AS ??,
       Isnull([11], 0) AS ???,
       Isnull([12], 0) AS ???
FROM   (SELECT Year(Class_Date)  AS Class_Year,
               Month(Class_Date) AS Class_Month,
               studentcount
        FROM   (SELECT Dateadd(day, Sequence_id, '2011-1-1') AS Class_Date
                FROM   E_Sequence) t1,
               e_classes t2
        WHERE  t1.Class_Date BETWEEN t2.begindate AND t2.enddate) t 
        PIVOT (Sum(studentcount) FOR Class_Month IN
        ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) tpivot

???????????????????????????

???

SELECT Dateadd(day, Sequence_id, '2011-1-1') AS Class_Date FROM E_Sequence

????????????????????????????????????????????????

???????

????2012?07?16??2013?06?15??????????????????