{"id":319466,"date":"2024-01-21T13:00:34","date_gmt":"2024-01-21T05:00:34","guid":{"rendered":"https:\/\/www.idc.net\/help\/319466\/"},"modified":"2024-01-21T13:00:34","modified_gmt":"2024-01-21T05:00:34","slug":"sql%e4%b8%ad%e8%bf%94%e5%9b%9e%e8%ae%a1%e7%ae%97%e8%a1%a8%e8%be%be%e5%bc%8f%e7%9a%84%e5%87%bd%e6%95%b0","status":"publish","type":"post","link":"https:\/\/idc.net\/help\/319466\/","title":{"rendered":"SQL\u4e2d\u8fd4\u56de\u8ba1\u7b97\u8868\u8fbe\u5f0f\u7684\u51fd\u6570"},"content":{"rendered":"

\u4e0b\u9762\u4e3a\u60a8\u4ecb\u7ecd\u4e00\u4e2aSQL\u4e2d\u8fd4\u56de\u8ba1\u7b97\u8868\u8fbe\u5f0f\u7684\u51fd\u6570\uff0c\u8be5\u51fd\u6570\u53ef\u4ee5\u8ba1\u7b97\u52a0\u3001\u51cf\u3001\u4e58\u3001\u9664\uff0c\u4f46\u662f\u4e0d\u80fd\u7b97\u5e42\uff0c\u4f9b\u60a8\u53c2\u8003\uff0c\u5e0c\u671b\u5bf9\u60a8\u5b66\u4e60SQL\u4e2d\u7684\u51fd\u6570\u80fd\u6709\u6709\u6240\u5e2e\u52a9\u3002<\/p>\n

 <\/p>\n

\n \n \n 
    \n  \n  \n  
  1. CREATE FUNCTION dbo.GetExp(@pstrExpress AS VARCHAR(8000))  <\/span><\/span><\/li>\n  \n  \n  
  2. RETURNS DECIMAL(18,6) AS  <\/span><\/li>\n  \n  \n  
  3. BEGIN  <\/span><\/li>\n  \n  \n  
  4. DECLARE @i INT,@j INT   <\/span><\/li>\n  \n  \n  
  5. DECLARE @c1 CHAR(1),@c2 CHAR(1),@c VARCHAR(100)  <\/span><\/li>\n  \n  \n  
  6. DECLARE @v1 DECIMAL(18,6),@v2 DECIMAL(18,6),@v DECIMAL(18,6)  <\/span><\/li>\n  \n  \n  
  7. DECLARE @t TABLE(ID INT IDENTITY(1,1),s VARCHAR(100))  <\/span><\/li>\n  \n  \n  
  8. DECLARE @s TABLE(ID INT IDENTITY(1,1),s VARCHAR(100))  <\/span><\/li>\n  \n  \n  
  9. DECLARE @sv TABLE(ID INT IDENTITY(1,1),v DECIMAL(18,6))  <\/span><\/li>\n  \n  \n  
  10.  <\/span><\/li>\n  \n  \n  
  11. SET @<\/span>pstrExpress<\/span> = <\/span>REPLACE<\/span>(@pstrExpress,' ','')  <\/span><\/li>\n  \n  \n  
  12. SELECT @<\/span>i<\/span> = <\/span>0<\/span>,@<\/span>j<\/span> = <\/span>LEN<\/span>(@pstrExpress),@<\/span>c2<\/span> = <\/span>''<\/span>,@<\/span>c<\/span> = <\/span>''<\/span> <\/span><\/li>\n  \n  \n  
  13. WHILE @i<\/span><<\/span>@j  <\/span><\/li>\n  \n  \n  
  14. BEGIN  <\/span><\/li>\n  \n  \n  
  15.  SELECT @<\/span>c1<\/span> = @c2,@<\/span>i<\/span> = @i+1  <\/span><\/li>\n  \n  \n  
  16.  SELECT @<\/span>c2<\/span> = <\/span>SUBSTRING<\/span>(@pstrExpress,@i,1)  <\/span><\/li>\n  \n  \n  
  17.  IF CHARINDEX(@c2,'.0123456789') <\/span>><\/span> 0 or (@<\/span>c2<\/span> = <\/span>'-'<\/span> and @c1 IN('','*','-','+','\/','('))  <\/span><\/li>\n  \n  \n  
  18.  BEGIN   SELECT @<\/span>c<\/span> = @c + @c2   CONTINUE  END  <\/span><\/li>\n  \n  \n  
  19.  IF @c <\/span><<\/span>><\/span> ''  BEGIN INSERT @t(s)  SELECT @c SELECT @<\/span>c<\/span> = <\/span>''<\/span> END  <\/span><\/li>\n  \n  \n  
  20.  IF CHARINDEX(@c2,')')<\/span>><\/span>0  <\/span><\/li>\n  \n  \n  
  21.  BEGIN   <\/span><\/li>\n  \n  \n  
  22.    INSERT @t(s)  SELECT s FROM @s WHERE ID <\/span>><\/span> ISNULL((SELECT MAX(ID) FROM @s WHERE s IN('(')),0) ORDER BY ID DESC  <\/span><\/li>\n  \n  \n  
  23.    DELETE @s WHERE ID <\/span>><\/span>= ISNULL((SELECT MAX(ID) FROM @s WHERE s IN('(')),0)   <\/span><\/li>\n  \n  \n  
  24.    CONTINUE  <\/span><\/li>\n  \n  \n  
  25.  END  <\/span><\/li>\n  \n  \n  
  26.  IF CHARINDEX(@c2,'+-)')<\/span>><\/span>0  <\/span><\/li>\n  \n  \n  
  27.  BEGIN   <\/span><\/li>\n  \n  \n  
  28.    INSERT @t(s)  SELECT s FROM @s WHERE ID <\/span>><\/span> ISNULL((SELECT MAX(ID) FROM @s WHERE s IN('(')),0) ORDER BY ID DESC  <\/span><\/li>\n  \n  \n  
  29.    DELETE @s WHERE ID <\/span>><\/span> ISNULL((SELECT MAX(ID) FROM @s WHERE s IN('(')),0)   <\/span><\/li>\n  \n  \n  
  30.    IF @c2 <\/span><<\/span>><\/span> ')' INSERT @s(s) SELECT @c2  <\/span><\/li>\n  \n  \n  
  31.    CONTINUE  <\/span><\/li>\n  \n  \n  
  32.  END  <\/span><\/li>\n  \n  \n  
  33.  IF CHARINDEX(@c2,'*\/')<\/span>><\/span>0  <\/span><\/li>\n  \n  \n  
  34.  BEGIN   <\/span><\/li>\n  \n  \n  
  35.    INSERT @t(s)  SELECT s FROM @s WHERE ID <\/span>><\/span> ISNULL((SELECT MAX(ID) FROM @s WHERE s IN('(','+','-')),0) ORDER BY ID DESC  <\/span><\/li>\n  \n  \n  
  36.    DELETE @s WHERE ID <\/span>><\/span> ISNULL((SELECT MAX(ID) FROM @s WHERE s IN('(','+','-')),0)   <\/span><\/li>\n  \n  \n  
  37.    INSERT  @s SELECT @c2  <\/span><\/li>\n  \n  \n  
  38.    CONTINUE  <\/span><\/li>\n  \n  \n  
  39.  END  <\/span><\/li>\n  \n  \n  
  40.  IF CHARINDEX(@c2,'(')<\/span>><\/span>0 INSERT  @s SELECT @c2  <\/span><\/li>\n  \n  \n  
  41. END  <\/span><\/li>\n  \n  \n  
  42. IF @c <\/span><<\/span>><\/span> '' INSERT @t(s) SELECT @c  <\/span><\/li>\n  \n  \n  
  43. INSERT @t(s)  SELECT s FROM @s ORDER BY ID DESC  <\/span><\/li>\n  \n  \n  
  44. SELECT @<\/span>i<\/span> = <\/span>0<\/span>,@<\/span>j<\/span> = <\/span>MAX<\/span>(ID) FROM @t   <\/span><\/li>\n  \n  \n  
  45. WHILE @i <\/span><<\/span> @j  <\/span><\/li>\n  \n  \n  
  46. BEGIN   <\/span><\/li>\n  \n  \n  
  47.  SELECT @<\/span>i<\/span> = @i + 1  <\/span><\/li>\n  \n  \n  
  48.  SELECT @<\/span>c<\/span> = <\/span>s<\/span> FROM @t WHERE <\/span>ID<\/span> = @i  <\/span><\/li>\n  \n  \n  
  49.  IF @<\/span>c<\/span> = <\/span>'('<\/span> CONTINUE  <\/span><\/li>\n  \n  \n  
  50.  IF @c NOT IN('*','-','+','\/')  BEGIN  INSERT @sv(v) SELECT CONVERT(float,@c) CONTINUE END  <\/span><\/li>\n  \n  \n  
  51.  SELECT @<\/span>v<\/span>v2<\/span> = v FROM @sv  DELETE @sv  WHERE <\/span>ID<\/span> = (SELECT MAX(ID) FROM @sv)  <\/span><\/li>\n  \n  \n  
  52.  SELECT @<\/span>v<\/span>v1<\/span> = v FROM @sv  DELETE @sv  WHERE <\/span>ID<\/span> = (SELECT MAX(ID) FROM @sv)  <\/span><\/li>\n  \n  \n  
  53.  SELECT @<\/span>v<\/span> = <\/span>CASE<\/span> @c WHEN '+' THEN @v1 + @v2 WHEN '-' THEN @v1 - @v2  <\/span><\/li>\n  \n  \n  
  54.                      WHEN '*' THEN @v1 * @v2 WHEN '\/' THEN @v1 \/ @v2 END  <\/span><\/li>\n  \n  \n  
  55.  INSERT @sv(v) SELECT @v  <\/span><\/li>\n  \n  \n  
  56. END  <\/span><\/li>\n  \n  \n  
  57. SELECT @<\/span>v<\/span>v<\/span> = v FROM @sv  <\/span><\/li>\n  \n  \n  
  58. RETURN @v  <\/span><\/li>\n  \n  \n  
  59. END <\/span><\/li>\n \n \n <\/ol><\/pre>\n
     <\/p>\n
     <\/p>\n
    \u3010\u7f16\u8f91\u63a8\u8350\u3011<\/p>\n
    SQL\u4e2dDATENAME\u51fd\u6570\u7684\u7528\u6cd5<\/p>\n
    SQL\u4e2d\u5faa\u73af\u8bed\u53e5\u7684\u6548\u679c\u5b9e\u4f8b<\/p>\n
    SQL\u4e2d\u7c7b\u4f3cFor\u5faa\u73af\u5904\u7406\u7684\u5b9e\u4f8b<\/p>\n
    \u5bf9\u5b58\u50a8\u8fc7\u7a0b\u4ee3\u66ffSQL\u8bed\u53e5\u7684\u8ba8\u8bba<\/p>\n
    SQL\u805a\u5408\u51fd\u6570\u4e4bAvg \u51fd\u6570<\/p>\n
     <\/p>\n","protected":false},"excerpt":{"rendered":"
    \u4e0b\u9762\u4e3a\u60a8\u4ecb\u7ecd\u4e00\u4e2aSQL\u4e2d\u8fd4\u56de\u8ba1\u7b97\u8868\u8fbe\u5f0f\u7684\u51fd\u6570\uff0c\u8be5\u51fd\u6570\u53ef\u4ee5\u8ba1\u7b97\u52a0\u3001\u51cf\u3001\u4e58\u3001\u9664\uff0c\u4f46\u662f\u4e0d\u80fd\u7b97\u5e42\uff0c\u4f9b\u60a8\u53c2\u8003\uff0c\u5e0c\u671b\u5bf9\u60a8\u5b66 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7038],"tags":[],"class_list":["post-319466","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/posts\/319466","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/comments?post=319466"}],"version-history":[{"count":0,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/posts\/319466\/revisions"}],"wp:attachment":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/media?parent=319466"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/categories?post=319466"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/tags?post=319466"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}